Aggregate Functions

Every query so far handed back rows. Aggregate functions hand back answers: how many, how much, largest, average. Add GROUP BY and you get one answer per category, which is most of what the word "analytics" means.

One value out of many rows

Picture a trips table a taxi dispatcher keeps, one row per completed trip:

| driver | minutes |
| ana    |      30 |
| ben    |      12 |
| ana    |      45 |
| ben    |      18 |
| cara   |      25 |

An aggregate function reads a whole column and collapses it into a single value:

SELECT COUNT(*) FROM trips;      -- 5
SELECT SUM(minutes) FROM trips;  -- 130
SELECT MIN(minutes) FROM trips;  -- 12
SELECT MAX(minutes) FROM trips;  -- 45
SELECT AVG(minutes) FROM trips;  -- 26.0

However many rows go in, one row comes out. Three details worth pinning down now:

• COUNT(*) counts rows. COUNT(minutes) counts the rows whose minutes is not NULL. COUNT(DISTINCT driver) counts different values, so it answers 3 here and not 5. Three questions, three numbers.
• Every aggregate except COUNT(*) skips NULLs. AVG over {10, NULL, 20} is 15, because it divides by two values, not three.
• SUM over whole numbers stays whole, but AVG always produces a decimal. That 26.0 is not a typo, it is exactly what the runner prints, and an exact-output grader counts the .0.

GROUP BY: one answer per pile

"How much did each driver drive?" does not want one number, it wants one number per driver.

SELECT driver, COUNT(*), MAX(minutes)
FROM trips
GROUP BY driver;

ana|2|45
ben|2|18
cara|1|25

GROUP BY driver splits the five rows into piles that share a driver, and each aggregate then runs inside its own pile. One output row per pile. (As always in this runner, values are printed joined by | with no header line.)

The golden rule: every column in the SELECT list must be either inside an aggregate or named in the GROUP BY. A bare plate_number next to GROUP BY driver has no single answer per pile; Postgres rejects that query outright, while SQLite quietly picks one row's value and lets you ship the bug.

HAVING: filtering the piles

WHERE runs before grouping, so it can only see individual rows: no aggregate exists yet for it to test. Filtering on an aggregate has its own keyword.

SELECT driver, SUM(minutes)
FROM trips
WHERE minutes >= 15        -- row filter: short trips never enter a pile
GROUP BY driver
HAVING COUNT(*) > 1;       -- pile filter: drivers with 2 or more surviving trips

The mnemonic: WHERE picks the players, HAVING picks the teams.

Common mistakes

• Dropping the label column. SELECT SUM(minutes) FROM trips GROUP BY driver still produces one row per driver, but prints 75, 30, 25 and nothing saying whose minutes those are. A grouped report needs the grouping column selected too.
• Forgetting GROUP BY. Without it the whole table is one pile, so you get the single line 130 where you wanted one line per driver.
• Filtering an aggregate with WHERE. WHERE SUM(minutes) > 50 fails with "misuse of aggregate function"; that condition belongs in HAVING.
• Expecting AVG to be a whole number. It prints 26.0, so an exact comparison against 26 fails. Wrap it when you need an integer: CAST(AVG(minutes) AS INTEGER).

Your exercise

The products table and its rows are already written at the top of the editor; you add the one query underneath. Ask for the grouping column and the aggregate: a query that names only the total prints a column of unlabelled numbers, which is the most common way this exercise is failed.