Summarise data per group with COUNT, SUM and AVG, and filter groups with HAVING.
Review first: GROUP BY
All problems use this sales table:
Return each region and its total amount as revenue, highest revenue first.
GROUP BY region, SUM(amount), then ORDER BY revenue DESC.
SELECT region, SUM(amount) AS revenue
FROM sales
GROUP BY region
ORDER BY revenue DESC;region | revenue
-------+--------
North | 189000
South | 123000
East | 60000For each product, return the number of sales (sales_count) and the total units sold (units).
Use COUNT(*) and SUM(quantity) together.
SELECT product,
COUNT(*) AS sales_count,
SUM(quantity) AS units
FROM sales
GROUP BY product
ORDER BY product;product | sales_count | units
--------+-------------+------
Laptop | 2 | 3
Phone | 2 | 8
Tablet | 2 | 6Return the regions that made more than one sale and whose average sale amount is above 50,000.
Both conditions are about groups, so both go in HAVING.
SELECT region,
COUNT(*) AS sales_count,
AVG(amount) AS avg_amount
FROM sales
GROUP BY region
HAVING COUNT(*) > 1
AND AVG(amount) > 50000;region | sales_count | avg_amount
-------+-------------+-----------
North | 3 | 63000
South | 2 | 61500East has only one sale, so COUNT(*) > 1 removes it. North averages 189,000 / 3 = 63,000 and South averages 123,000 / 2 = 61,500.