Summarize and streamline immediately

Question

I have an order table. There I need to find out which 3 partner_id made the largest amount_totals and sort them from 3 to the smallest.

testdb=# SELECT amount_total, partner_id FROM sale_order; amount_total | partner_id --------------+------------ 1244.00 | 9 3065.90 | 12 3600.00 | 3 2263.00 | 25 3000.00 | 10 3263.00 | 3 123.00 | 25 5400.00 | 12 (8 rows) 

Just by running SQL, I find this confusing ...

Answer 1

Total Amounts

If you want to transfer aggregated amounts, it could be simple:

 SELECT partner_id, sum(amount_total) AS amout_suptertotal FROM sale_order GROUP BY 1 ORDER BY 2 DESC LIMIT 3; 

1 in GROUP BY 1 is a numeric parameter that refers to a position in the SELECT list. Just a conditional shortcut for GROUP BY partner_id in this case.

This ignores the special case when more than three partners will qualify and choose 3 arbitrarily (due to lack of definition).

Individual amounts

 SELECT partner_id, amount_total FROM sale_order JOIN ( SELECT partner_id, rank() OVER (ORDER BY sum(amount) DESC) As rnk FROM sale_order GROUP BY 1 ORDER BY 2 LIMIT 3 ) top3 USING (partner_id) ORDER BY top3.rnk; 

This, on the other hand, includes all peers, if more than 3 partners are entitled to the top 3. The window rank() function gives you this.

The method here is grouping partner_id in the subquery top3 and the window function rank() top3 ranks after aggregation (window functions are executed after aggregate functions). ORDER BY is applied after window functions, and LIMIT is applied last. All in one subquery.

Then I join the base table to this subquery, so that only the top dogs remain as a result and in rnk order.

Window functions require PostgreSQL 8.4 or later.

This is pretty advanced stuff. You should start learning SQL with something simpler, perhaps.

Answer 2

 select amount_total, partner_id from ( select sum(amount_total) amount_total, partner_id from sale_order group by partner_id ) s order by amount_total desc limit 3