GROUP BY and HAVING

Why this stage exists

The previous stage answered how many employees are there? The next question is always how many in each department? GROUP BY splits the rows into groups, one per distinct key, and runs the aggregates once per group, so the result has one row per department:

>select dept, count(*), sum(salary) from emp group by dept;
dept | COUNT(*) | SUM(salary)
eng | 3 | 320
ops | 2 | 170
NULL | 2 | 110
hr | 1 | 70
(4 rows)
[EXEC] rows_examined=8 groups=4

Rows whose dept is NULL form one group of their own: for grouping, NULL is the same as NULL, even though NULL = NULL is not true in a WHERE. SQL draws that line on purpose: a group is about rows being alike, and two missing values are alike.

Two ways to build groups

Hash aggregation keeps a table with one entry per group, found by the group's key values. Each row is looked up, its group is created if it is new, and its aggregates take one step. It works for rows in any order, and holds every group until the input ends.

Sort aggregation relies on the input being in key order. Then all the rows of a group arrive together: step the current group while the key stays the same, and when it changes, the current group is finished and can be handed up at once. Only one group is ever in memory.

The planner picks sort aggregation when it can prove the order: when the first GROUP BY key is the table's first column, since rows arrive in that order. Otherwise it plans hash aggregation. Real planners also sort first when that is cheaper, or use an index that delivers the order; this one keeps the rule simple.

Both give the groups in the same order: the order in which each group's first row arrived. For hash aggregation that is the order groups were created; for sort aggregation it is simply the input order. Since rows arrive in key order, the eng group, whose smallest id is 1, comes before the ops group, whose smallest is 2, wherever it ends up in a hash table.

What a group can show

A grouped query gives one row per group, so outside an aggregate it can only show what is the same for every row of the group: a key, written as the key is written, a column that is a key on its own, a literal, or an aggregate. select name, count(*) from emp group by dept asks for one name for three engineers, which is 01507. select dept, max(name) is fine: MAX picks one.

The rule reaches HAVING and ORDER BY too, since both run on groups. HAVING may use aggregates the select list does not show, such as having sum(salary) > 150 beside select dept.

WHERE, then GROUP BY, then HAVING

The clauses run in the order the plan stacks them, bottom up: the WHERE drops rows, the aggregate groups what is left, HAVING drops groups, ORDER BY sorts them, the Project computes the output, and LIMIT keeps the first few:

>explain select dept, count(*) from emp where salary > 50 group by dept having count(*) > 1 order by dept;
[PLAN] select dept, count(*) from emp where salary > 50 group by dept having count(*) > 1 order by dept
[PLAN]   └── Project (cols=[dept, COUNT(*)])
[PLAN]       └── Sort (key=dept ASC)
[PLAN]           └── Filter (COUNT(*) > 1)
[PLAN]               └── HashAggregate (keys=[dept], aggs=[COUNT(*)])
[PLAN]                   └── Filter (salary > 50)
[PLAN]                       └── SeqScan (table=emp)

HAVING is just a Filter with aggregates in it, placed above the aggregate. That is the whole difference between WHERE and HAVING: where in the plan they sit.

HAVING also works without GROUP BY. Then every row the WHERE keeps is one group, as in the previous stage, and HAVING decides whether that one group is printed. This is the one exception to the previous stage's "always one row": select count(*) from t having count(*) > 0 prints its one row, but having count(*) > 100 prints the header and (0 rows). A bare column beside it is still 01507, since one group has no single value for it: select id from t having id > 1.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 48.

groups counts the groups formed, before HAVING drops any, and comes after sort_runs when both are printed: [EXEC] rows_examined=8 sort_runs=1 groups=4. A statement without GROUP BY keeps the Aggregate node and prints no groups.

What you should see when done

$droid --db staff.db
Welcome to droid!
>select dept, count(*) as n, avg(salary) from emp group by dept having count(*) > 1 order by n desc;
dept | n | AVG(salary)
eng | 3 | 106.67
ops | 2 | 85.00
NULL | 2 | 55.00
(3 rows)
[EXEC] rows_examined=8 sort_runs=1 groups=4
>select dept, name, count(*) from emp group by dept;
[ERROR:01507] Column name must appear in GROUP BY or in an aggregate
>select id, salary from emp where id <= 3 group by id, salary;
id | salary
1 | 100
2 | 90
3 | 120
(3 rows)
[EXEC] rows_examined=3 groups=3
>.exit
exiting.. good bye!

The first query formed four groups and HAVING kept three; the ties on n = 2 stayed in first-seen order, ops before NULL, because the sort is stable. The last query groups by the first column, so it ran as a SortAggregate. The messages are yours to word; the tests check only the codes.

Common bugs in this stage

• Groups in hash-table order. Iterating the hash table gives a different order from run to run, or from one implementation to the next. Keep the groups in a list in creation order.
• Keys glued together. Building the key as x || y merges ('ab', 'c') with ('a', 'bc'). Compare the values one by one, or encode each with its length.
• NULL keys never equal. Using the WHERE's = makes every NULL row its own group. For grouping, NULL equals NULL.
• HAVING before the aggregates finish. HAVING needs the finished values of the group, not the running ones.
• WHERE with aggregates accepted. An aggregate in the WHERE is still 01508; it belongs in HAVING.
• The ungrouped-column check skipped for HAVING or ORDER BY. Both run on groups, and both are 01507 for a bare column.
• groups counted after HAVING. It counts the groups formed.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C and C++: C has no hash table, so write a small one keyed by a hash of the key values, with each bucket holding indexes into the creation-order array. In C++, std::unordered_map with a custom hash of the key tuple works, next to a std::vector of groups in creation order.
• Rust: a HashMap<Vec<Value>, usize> from key tuple to an index into a Vec of groups keeps first-seen order; derive Hash and Eq for your value type with NULL equal to NULL. indexmap::IndexMap does both at once if you allow a crate.
• Zig: std.HashMap with a context that hashes and compares key tuples, and an ArrayList of groups in creation order.