Aggregate Functions

Everything so far returned rows. Aggregates return answers: how many, how much, biggest, average. This is SQL crossing from storage into analysis — and with GROUP BY, it's most of what "analytics" means.

The five you'll use forever

SELECT COUNT(*)      FROM orders;   -- how many rows
SELECT SUM(amount)   FROM orders;   -- total
SELECT AVG(amount)   FROM orders;   -- mean
SELECT MIN(amount), MAX(amount) FROM orders;

An aggregate collapses many rows into one value. Two footnotes that separate the fluent from the lucky:

• COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL; COUNT(DISTINCT column) counts distinct values. Three different questions, three different numbers.
• Aggregates ignore NULLs (except COUNT(*)) — AVG of {10, NULL, 20} is 15, not 10. Usually what you want, occasionally a surprise; always worth knowing which.

GROUP BY: aggregates per category

The headline act. "Total per category" doesn't want one number — it wants one number per group:

SELECT category, SUM(amount) AS total
FROM orders
GROUP BY category;

| category | total |
| books    |   340 |
| games    |   125 |

GROUP BY category partitions the rows by that column's value; every aggregate then runs within each partition. One output row per group.

The golden rule (memorize; it's also enforced by stricter databases): every column in the SELECT must be either inside an aggregate or listed in GROUP BY. Selecting a bare customer_name while grouping by category is meaningless — which customer, out of the many per group? SQLite permissively picks one arbitrarily; Postgres refuses to run it. Write to the strict standard and your SQL ports everywhere.

HAVING: filtering the groups themselves

WHERE filters rows before grouping — it can't see aggregate results. Filtering after aggregation gets its own keyword:

SELECT category, SUM(amount) AS total
FROM orders
WHERE status = 'paid'          -- row filter: only paid orders count at all
GROUP BY category
HAVING SUM(amount) > 200;      -- group filter: only categories that total > 200

The mnemonic: WHERE picks the players, HAVING picks the teams. Using WHERE on an aggregate (WHERE SUM(amount) > 200) is a syntax error — the moment you want to filter on an aggregate, the word is HAVING.

Your exercise: Total Per Category

Group and sum — the canonical GROUP BY:

SELECT category, SUM(amount) AS total
FROM orders
GROUP BY category
ORDER BY category;

Notes that decide the grade: alias the sum to the expected column name; add ORDER BY because grouped output order is otherwise arbitrary (last lesson's rule applies with extra force — group order genuinely varies); and check whether the spec wants categories with no qualifying rows (a plain GROUP BY only produces groups that exist in the data). Aggregate + group + order: with those three moves you can answer most questions anyone will ever ask of a table.