# Summation and order at once

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 ...

### Aggregated amounts

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).

### 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 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 `rnk`.

Window functions require PostgreSQL 8.4 or later.

This is rather advanced stuff. You should start learning SQL with something simpler probably.