I have a table of orders. There I need to find out which 3 partner_id's have made the largest sum of amount_totals, and sort those 3 from biggest to 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 starting SQL, I find it confusing ...
If you want to list aggregated amounts, it can be as simple as:
SELECT partner_id, sum(amount_total) AS amout_suptertotal FROM sale_order GROUP BY 1 ORDER BY 2 DESC LIMIT 3;
The 1 in
GROUP BY 1 is a numerical parameter, that refers to the position in the
SELECT list. Just a notational shortcut for
GROUP BY partner_id in this case.
This ignores the special case where more than three partner would qualify and picks 3 arbitrarily (for lack of definition).
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 one, on the other hand includes all peers if more than 3 partner qualify for the top 3. The window function
rank() gives you that.
The technique here is to group by
partner_id in the subquery
top3 and have the window function
rank() attach ranks after the aggregation (window functions execute 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 in the result and order by
Window functions require PostgreSQL 8.4 or later.
This is rather advanced stuff. You should start learning SQL with something simpler probably.