Total and average per group
Add up and average a number for every value of a column, with GROUP BY.
SELECT status,
COUNT(*) AS orders,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM orders
GROUP BY status
ORDER BY total_amount DESC;How it works
GROUP BY puts the rows with the same status into one group. COUNT, SUM and AVG are then calculated once per group, so the result has one row per status.
Every column in the SELECT list must either be in GROUP BY or be inside an aggregate function such as SUM.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paidExpected result
status | orders | total_amount | avg_amount
paid | 7 | 1320.0 | 188.57142857142858
refunded | 1 | 50.0 | 50.0Common mistakes and limitations
- Selecting a column that is neither in GROUP BY nor inside an aggregate. Most databases refuse it; MySQL in some modes returns an arbitrary value, which is worse. - Using WHERE to filter on an aggregate. Use HAVING for conditions on SUM, COUNT and so on. - AVG ignores NULL values, so the average can differ from SUM divided by COUNT(*).