Aggregate Functions, GROUP BY and HAVING

Aggregate Functions

An aggregate function takes many rows and returns one number.

You can combine several in one query and name them: SELECT COUNT(*) AS orders, SUM(quantity) AS units FROM orders;

Good to know: aggregates ignore NULL values. AVG of 10, NULL and 20 is 15, not 10.

GROUP BY

Aggregates on their own give one number for the whole table. GROUP BY splits the rows into groups first, then calculates the aggregate for each group, just like a PivotTable.

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

This returns one row per category with its product count and average price.

The golden rule: every column in SELECT must either be in the GROUP BY or be inside an aggregate function. SELECT category, product_name, COUNT(*) ... GROUP BY category fails (or gives misleading results) because SQL cannot show one product name for a whole group.

You can group by more than one column: GROUP BY category, city gives one row for each combination.

HAVING Clause

HAVING filters groups after they are summarized. WHERE filters rows before they are grouped.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2026-01-01'   -- rows: only 2026 orders
GROUP BY customer_id
HAVING COUNT(*) >= 3                -- groups: only customers with 3+ orders
ORDER BY order_count DESC;

How SQL runs it: FROM picks the table, WHERE removes rows, GROUP BY forms groups, HAVING removes groups, SELECT picks the output, ORDER BY sorts it. That is why you cannot use an aggregate such as COUNT(*) in WHERE: the groups do not exist yet.