Group sales data by one or more columns, combine WHERE with HAVING, and filter groups by their totals, averages and counts.
Review first: GROUP BY, HAVING, Aggregate Functions
All problems use this sales table:
For each region, return the total amount as revenue and the number of sales as sales. Show the highest revenue first.
GROUP BY region, with SUM and COUNT in the SELECT.
SELECT region,
SUM(amount) AS revenue,
COUNT(*) AS sales
FROM sales
GROUP BY region
ORDER BY revenue DESC;region | revenue | sales
-------+---------+------
North | 320000 | 5
South | 160000 | 3
East | 85000 | 2Return the total units sold for every combination of region and product as units, sorted by region and then product.
List both columns in GROUP BY. Each unique pair becomes one group.
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY region, product
ORDER BY region, product;region | product | units
-------+---------+------
East | Laptop | 1
East | Phone | 2
North | Laptop | 2
North | Phone | 9
North | Tablet | 5
South | Laptop | 1
South | Phone | 5
South | Tablet | 2Return each rep whose total sales amount is more than 150000, with that total as total. Highest total first.
You cannot use SUM() in WHERE. Filter groups with HAVING.
SELECT rep, SUM(amount) AS total
FROM sales
GROUP BY rep
HAVING SUM(amount) > 150000
ORDER BY total DESC;rep | total
-----+-------
Asha | 170000
Ravi | 160000Kiran's total is exactly 150000, so "more than 150000" (>) excludes Kiran. Read limits in a question carefully; >= would include Kiran.
Considering Phone sales only, return each rep who sold at least 5 phone units in total, with the units as phone_units. Most units first.
WHERE keeps only Phone rows before grouping; HAVING then filters the groups.
SELECT rep, SUM(units) AS phone_units
FROM sales
WHERE product = 'Phone'
GROUP BY rep
HAVING SUM(units) >= 5
ORDER BY phone_units DESC;rep | phone_units
------+------------
Kiran | 6
Ravi | 5Filtering product = 'Phone' in WHERE is correct and efficient, because it is a condition on individual rows. Only conditions on aggregates (like SUM(units)) belong in HAVING.
Return each product whose average sale amount is above 50000, with the average rounded to 2 decimals as avg_amount.
HAVING AVG(amount) > 50000.
SELECT product, ROUND(AVG(amount), 2) AS avg_amount
FROM sales
GROUP BY product
HAVING AVG(amount) > 50000
ORDER BY avg_amount DESC;product | avg_amount
--------+-----------
Laptop | 73333.33
Phone | 60000The Laptop average is (110000 + 55000 + 55000) / 3 = 73333.33 and the Phone average is exactly 60000. The Tablet average is 35000, so it is filtered out.
There are three products. Return the reps who sold all three of them.
Count the distinct products per rep and keep the reps where that count is 3.
SELECT rep
FROM sales
GROUP BY rep
HAVING COUNT(DISTINCT product) = 3
ORDER BY rep;rep
----
Asha
RaviTo avoid hard-coding the 3, compare with a subquery instead, HAVING COUNT(DISTINCT product) = (SELECT COUNT(DISTINCT product) FROM sales). The query keeps working when a fourth product is added.
For each region, return the biggest sale as biggest, the smallest as smallest, and the difference between them as spread. Sort by region.
You can do arithmetic with aggregate results, for example MAX(amount) - MIN(amount).
SELECT region,
MAX(amount) AS biggest,
MIN(amount) AS smallest,
MAX(amount) - MIN(amount) AS spread
FROM sales
GROUP BY region
ORDER BY region;region | biggest | smallest | spread
-------+---------+----------+-------
East | 55000 | 30000 | 25000
North | 110000 | 15000 | 95000
South | 75000 | 30000 | 45000