Aggregates and GROUP BY

An aggregate function takes many rows and collapses them into a single number. SELECT AVG(price) FROM products returns one row, however many products there are. That is the whole idea — and GROUP BY is how you say “do that once per group” rather than once for the entire table.

We will work on this product table all lesson:

id	name	category	price
1	Pencil	Stationery	2
2	Notebook	Stationery	6
3	Coffee	Grocery	9
4	Laptop	Electronics	900
5	Cable	Electronics	12

The five you reach for

COUNT(*) counts rows, SUM totals, AVG averages, MIN and MAX take the extremes. When every column you select is an aggregate, the engine treats the whole table as one big group and hands back a single row:

SELECT COUNT(*) AS n,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       SUM(price) AS catalog_value,
       AVG(price) AS avg_price
FROM   products;

n	cheapest	dearest	catalog_value	avg_price
5	2	900	929	185.8

The three flavours of COUNT

This is the question interview panels open with. The three are not interchangeable the moment NULLs are involved. Suppose a tiny orders table where one product is unrecorded:

order_id	user_id	product_id
1	7	4
2	7	NULL
3	9	4

SELECT COUNT(*)                AS total_rows,
       COUNT(product_id)       AS known_products,
       COUNT(DISTINCT user_id) AS distinct_buyers
FROM   orders;

total_rows	known_products	distinct_buyers
3	2	2

COUNT(*) counts every row; COUNT(col) counts only rows where col is not NULL (so it drops order 2); COUNT(DISTINCT col) counts unique non-NULL values (users 7 and 9). Write COUNT(email) on a table where some users have no email and you will quietly under-count — a classic silent bug.

GROUP BY — one result per group

Add GROUP BY and the aggregate runs once for each distinct value of the grouping column:

SELECT category,
       COUNT(*)   AS n,
       AVG(price) AS avg_price
FROM   products
GROUP BY category
ORDER BY avg_price DESC;

category	n	avg_price
Electronics	2	456
Grocery	1	9
Stationery	2	4

The one rule to remember: every column in SELECT must either appear in GROUP BY or sit inside an aggregate — otherwise the engine has many rows for that column and no way to pick one. (You can group by several columns too; each unique combination becomes one row.)

WHERE filters rows; HAVING filters groups

This is where people stumble. WHERE runs before grouping and filters individual rows; HAVING runs after grouping and filters whole groups. And you cannot put an aggregate in WHERE, because at that stage the aggregate has not been computed yet.

SELECT category, AVG(price) AS avg_price
FROM   products
GROUP BY category
HAVING AVG(price) > 10;

category	avg_price
Electronics	456

Only Electronics survives — Grocery (9) and Stationery (4) average below 10. Compare the two clauses as a pair: WHERE price > 10 keeps the pricey rows and then groups them; HAVING AVG(price) > 10 groups everything and then keeps the pricey groups. Different question, different answer. Prefer WHERE when the condition is about a row (it is faster — fewer rows to group); use HAVING only when the condition is about the group.

Practice

Quick check

0/3

Q1What is the difference between COUNT(*) and COUNT(email)?

They are identicalCOUNT(*) is faster but identicalCOUNT(email) skips rows where email is NULLCOUNT(*) excludes duplicates

Check answer

Q2SELECT country, COUNT(*) FROM users WHERE COUNT(*) > 1 GROUP BY country — why does it fail?

COUNT(*) needs a column nameWHERE can't reference aggregates — they don't exist until after grouping; use HAVINGGROUP BY must come before WHEREIt's missing ORDER BY

Check answer

Q3You want revenue per product, but only for products with at least 2 orders. Which structure is right?

WHERE COUNT(*) >= 2 GROUP BY productGROUP BY product HAVING COUNT(*) >= 2GROUP BY product WHERE COUNT(*) >= 2Use a subquery — there's no clean way

Check answer