How to order rows by group sum in SQL

Learn how to order rows by group sum in SQL with examples and explanations from SQLPad.

Problem

You have a table of data where each row belongs to a certain group. You want to order the rows based on the sum of a specific column for each group.

Sample data

Consider the following sample data in a sales table where each row represents a sales transaction:

id	salesperson	product	quantity
1	John	Apple	10
2	John	Banana	20
3	Mary	Apple	15
4	Mary	Orange	25
5	Peter	Banana	30
6	Peter	Apple	20

MySQL Solution

SELECT salesperson, product, quantity
FROM sales
ORDER BY salesperson, (SELECT SUM(quantity) FROM sales s2 WHERE s2.salesperson = sales.salesperson) DESC;

Explanation: MySQL Solution

In this solution, for each row in the sales table, a subquery is used to calculate the sum of the quantity column for rows with the same salesperson. This sum is used to order the rows. Rows with the same salesperson are grouped together and within each group, rows are ordered in descending order based on the sum of the quantity column.

PostgreSQL Solution

SELECT salesperson, product, quantity
FROM sales
ORDER BY salesperson, (SELECT SUM(quantity) FROM sales s2 WHERE s2.salesperson = sales.salesperson) DESC;

Explanation: PostgreSQL Solution

The solution for PostgreSQL is the same as MySQL. For each row in the sales table, a subquery calculates the sum of the quantity column for rows with the same salesperson. This sum is then used to order the rows. Rows with the same salesperson are grouped together and within each group, rows are ordered in descending order based on the sum of quantity.