Asking about groups, not rows

Every query so far returned rows. Most real questions are not about rows at all. How many orders last month. What is the average basket. Which customer has spent the most.

These are questions about groups, and SQL answers them with one clause.

Counting everything

SELECT COUNT(*) FROM orders;
SELECT SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders;

One row out, whatever the size of the table.

Counting per something

GROUP BY splits the rows into buckets and applies the function to each:

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

The rule that catches everyone: every column in the SELECT must either be in the GROUP BY or be inside an aggregate function. Ask for customer_name without grouping by it and the database has three names for one bucket and no way to choose. Postgres rejects it. MySQL used to pick one silently, which was worse.

WHERE and HAVING

They look similar and run at different times.

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-01'   -- filters ROWS, before grouping
GROUP BY customer_id
HAVING SUM(amount) > 10000;        -- filters GROUPS, after

WHERE cannot see an aggregate, because the groups do not exist yet. HAVING can, because they do. Putting a row condition in HAVING usually still works and is slower, because it grouped rows it was going to discard.

The order the database actually evaluates:

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

That order explains several things that otherwise look arbitrary, including why you cannot use a SELECT alias in WHERE but can in ORDER BY.

COUNT and NULL

COUNT(*) counts rows. COUNT(column) counts rows where that column is not null. On a table with nulls those are different numbers, and the gap between them is a common source of a report that does not reconcile.