How grouping and aggregation run

2.1 The pipeline with grouping added

Module one gave the logical order FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT. Grouping inserts two stages: GROUP BY after WHERE and HAVING right after GROUP BY. The full list is FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. WHERE sees raw rows, GROUP BY sees surviving rows, HAVING sees groups, and SELECT evaluates aggregates over those groups.

The order has three consequences worth memorising. A filter on a single row belongs in WHERE, because it can remove rows before the engine builds any bucket. A filter on an aggregate belongs in HAVING, because only there does the aggregate exist. A filter on an output column alias belongs in ORDER BY, because the alias is created in SELECT, which runs after both filters.

2.2 Sort, hash or index

An engine can group in two broad ways. The sort strategy sorts the rows by the grouping key, then walks the sorted stream and closes a group whenever the key changes. The hash strategy builds a hash table keyed by the group values and updates each bucket as rows arrive. Sort grouping produces output in key order and can spill to disk, while hash grouping is faster in memory and needs a final sort if the query asks for ordered output.

SQLite's planner leans toward sorting and shows the work as a temporary B-tree. The plan below reads the table and then adds USE TEMP B-TREE FOR GROUP BY, meaning it will sort or bucket the rows outside the table. An index whose leading columns match the grouping key can remove that step because the rows already arrive in key order. This is exactly the same trade-off as ORDER BY, which is why an index on a frequently grouped column earns its storage cost.

PostgreSQL reports strategies such as HashAggregate, GroupAggregate and MixedAggregate. The names differ, the trade-off does not: presorted input avoids the sort, memory pressure forces a spill, and a small number of groups fits comfortably in memory.

2.3 Tracing a grouped query

Take SELECT course_code, COUNT(*) AS n FROM enrollments WHERE status = 'active' GROUP BY course_code HAVING COUNT(*) >= 2 ORDER BY n DESC, course_code. The first block shows the rows that survive WHERE: ten active enrollments, each with a student and a course.

The grouping stage collapses those ten rows into five buckets, one per course that has at least one active enrollment. ML and WEB have one each. The others have two or three. HAVING COUNT(*) >= 2 then removes the two single-row buckets, leaving DS, SQL and PY. The final ORDER BY n DESC, course_code sorts the three surviving groups.

SELECT student_id, course_code, enrolled_on
FROM enrollments
WHERE status = 'active';

student_id | course_code | enrolled_on
-----------+-------------+------------
2          | SQL         | 2026-01-18
3          | PY          | 2026-02-02
4          | DS          | 2026-02-10
6          | SQL         | 2026-03-01
6          | ML          | 2026-03-02
7          | DS          | 2026-03-10
7          | WEB         | 2026-03-11
8          | SQL         | 2026-03-18
1          | DS          | 2026-02-20
4          | PY          | 2026-03-05

SELECT course_code, COUNT(*) AS n
FROM enrollments
WHERE status = 'active'
GROUP BY course_code;

course_code | n
------------+--
DS          | 3
ML          | 1
PY          | 2
SQL         | 3
WEB         | 1

SELECT course_code, COUNT(*) AS n
FROM enrollments
WHERE status = 'active'
GROUP BY course_code
HAVING COUNT(*) >= 2
ORDER BY n DESC, course_code;

course_code | n
------------+--
DS          | 3
SQL         | 3
PY          | 2

2.4 Bare columns and functional dependency

The standard rule says every expression in the select list must either appear in GROUP BY or be wrapped in an aggregate. The reason is simple: after grouping, a bare column has several possible values inside one group, and picking one of them is not a defined operation. SQLite picks a value from one of the rows and does not warn. PostgreSQL rejects the query unless it can prove the column is functionally dependent on the grouping key, and MySQL rejects it under ONLY_FULL_GROUP_BY. Functional dependency is accepted when the grouping key is the primary key of the table that holds the column.

The practical advice is to write the query as if the strict rule applied. If you need a representative value, make the choice explicit with MIN, MAX, or a subquery that selects the row you mean. A permissive engine makes the bug easier to ship, not easier to explain in review.

SELECT course_code, student_id, COUNT(*) AS n
FROM enrollments
GROUP BY course_code;

course_code | student_id | n
------------+------------+--
DS          | 1          | 3
ML          | 6          | 1
PY          | 2          | 4
SQL         | 1          | 5
WEB         | 7          | 1

2.5 Empty input and aggregate mechanics

Without GROUP BY, the engine treats the whole filtered result as one group even when that group has zero rows. That is why a global aggregate returns one row with COUNT(*) equal to 0 and SUM and AVG equal to NULL. With GROUP BY, zero input rows mean zero groups and therefore zero output rows. Neither case is an error, and an application that expects a number should wrap the aggregate in COALESCE.

DISTINCT inside an aggregate adds work because the engine must track the distinct values it has seen before it can count or average them. COUNT(DISTINCT column) also ignores NULLs, so it is not the same as COUNT(DISTINCT COALESCE(column, 'missing')), which treats the missing value as one more category. Pick the version that matches the question and say which one you used.

SELECT COUNT(*) AS rows_found,
       SUM(rating) AS rating_sum,
       AVG(rating) AS rating_avg,
       COUNT(DISTINCT rating) AS distinct_ratings
FROM enrollments
WHERE course_code = 'CN';

rows_found | rating_sum | rating_avg | distinct_ratings
-----------+------------+------------+-----------------
0          | NULL       | NULL       | 0

2.6 Cost and indexes

The cost of a grouped query is the filter cost plus the grouping cost plus the sort cost for ORDER BY. When the plan says USE TEMP B-TREE FOR GROUP BY, the grouping needs a temporary structure, and that structure may spill to disk when the result is large. An index on the grouping columns lets the engine read rows in key order, so group boundaries appear as it walks and the temporary B-tree disappears.

Look at the two plans below. The first groups enrollments by course_code without help. The second creates an index on that column and groups again. On this small table the difference is mostly about which structure the planner picks, but the pattern is the one to remember for large tables: match the index order to the grouping order, and put WHERE conditions that use the same index before the grouping.

EXPLAIN QUERY PLAN
SELECT course_code, COUNT(*) AS n
FROM enrollments
GROUP BY course_code;

CREATE INDEX idx_enrollments_course ON enrollments(course_code);

EXPLAIN QUERY PLAN
SELECT course_code, COUNT(*) AS n
FROM enrollments
GROUP BY course_code;

SCAN enrollments USING COVERING INDEX sqlite_autoindex_enrollments_1
USE TEMP B-TREE FOR GROUP BY
SCAN enrollments USING COVERING INDEX idx_enrollments_course

2.7 Where SQLite and PostgreSQL differ

Both engines agree on the important semantics: NULL forms its own group, aggregates skip NULL inputs, and HAVING filters groups. The differences sit in what the parser and planner allow, and those are the parts to name when an answer claims portability.

2.8 Common mistakes

• Filtering rows in HAVING and losing the chance to use an index on the filter column.
• Leaving a bare column in the select list because SQLite accepted it once.
• Expecting the groups to be sorted without ORDER BY.
• Comparing an empty SUM to 0 instead of handling NULL.
• Assuming COUNT(DISTINCT column) and COUNT(column) answer the same question.