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: select the grouping column and the aggregate, in that order, because the runner prints columns joined by | and the spec asks for category|total (an alias is good style but invisible to this grader, which prints no header row); and add ORDER BY, because grouped output order is otherwise arbitrary and the spec asks for alphabetical. One more from the failure log: SELECT FROM products with nothing between the two keywords is a syntax error — name what you want produced. Aggregate + group + order: with those three moves you can answer most questions anyone will ever ask of a table.