GROUP BY and HAVING: One Answer per Group

An aggregate collapses a whole table into one row. GROUP BY collapses it into one row per group: one count per region, one total per customer, the highest salary in each department.

SELECT region, COUNT(*), SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 300

The pipeline

A grouped query runs in a fixed order, and knowing it explains every rule:

1. WHERE keeps the rows that qualify. It sees single rows, so it cannot mention an aggregate.
2. GROUP BY partitions the surviving rows: two rows are in the same group when all the grouping columns are equal. NULL equals NULL for grouping, so all the NULLs of a column form one group.
3. The aggregates of the select list are computed once per group, over only that group's rows (NULL cells are skipped, exactly as in the previous lesson).
4. HAVING keeps the groups whose aggregate satisfies a condition: HAVING SUM(amount) > 300. This is the aggregate-level twin of WHERE; it needs the group's totals, so it can only run after step 3. WHERE amount > 100 drops rows before they are counted; HAVING COUNT(*) > 2 drops groups after.
5. The surviving groups are printed sorted by the grouping columns, ascending, comparing columns in order.

Group order and the value order

Cells in this lesson's tables have no declared type. A cell written NULL (or empty) is NULL; one that looks like an integer (-?digits, at most 15 digits) is an integer; anything else is text. The sort order is the one the whole engine uses: NULL first, then integers by numeric value, then text by bytes. So groups come out as NULL, -4, 9, 10, eu, us, and 10 comes after 9, not before it. Without this rule, comparing everything as text would put 10 before 9.

Which columns may be selected

With GROUP BY, the select list may contain the grouping columns and any aggregates. A plain column that is not a grouping column makes no sense (which of the group's values would it show?), so it is an error: column must appear in GROUP BY: name. Without GROUP BY the whole table is one group, so only aggregates are allowed, and you get one row even for an empty table (COUNT(*) is 0, SUM is NULL). With GROUP BY an empty table has no groups, so no rows. A grouping column need not be selected.

HAVING and numbers

HAVING <aggregate> <op> <integer> with =, !=, <, >, <= or >=. The group is kept only if the comparison is true. If the aggregate is NULL (say SUM over a group of NULLs) the comparison is unknown and the group is dropped, just like a WHERE on NULL. AVG is compared exactly: it is held in hundredths (the same integer rounding as before), and the number in HAVING is scaled the same way, so HAVING AVG(x) > 2 works without floating point. MIN and MAX of text compare with the same value order, so a text maximum is greater than any integer.

The protocol

TABLE sales region,amount          declare a table (column names, no spaces)
ROW sales us|100                   add a row, cells separated by |
QUERY SELECT region,COUNT(*) FROM sales GROUP BY region

QUERY takes SELECT items FROM table [WHERE col op literal] [GROUP BY col, ...] [HAVING aggregate op integer], keywords in any case. The WHERE is a single comparison with an integer, 'text' or NULL; a comparison with NULL, or on a NULL cell, is never true. The header is the select list (functions in upper case). SUM and AVG over a column that holds any text are an error: SUM needs integers: qty.

What you build

The starter parses commands, validates names, evaluates WHERE, computes the aggregates and prints rows. You write the two pieces that make a query "grouped":

• group_rows: partition and order the rows.
• passes: the HAVING test, with its NULL rule and the value order.

Common mistakes

• Comparing cells as text, so that 10 sorts before 9 and the NULL group is lost.
• Treating NULL cells as different groups, or dropping them.
• Filtering with HAVING what belongs in WHERE (or the reverse): the first changes what each group contains, the second only which groups are shown.
• Letting a NULL aggregate pass a HAVING.
• Rebuilding group order from first appearance instead of sorting by key.

Your exercise

You implement group_rows and passes. The mistake to avoid: grouping or ordering the cells as plain text.