Aggregates, GROUP BY and HAVING
Try it: Aggregates, GROUP BY and HAVING
How COUNT, SUM, AVG, MIN and MAX summarise the rows that survive WHERE, how GROUP BY computes one summary per group of equal keys, how HAVING then keeps only some groups, and how each aggregate treats NULL.
How it works
- WHERE removes rows first; only the remaining rows are summarised.
- GROUP BY sends each row to the group of its key value; all NULL keys share one group. Without GROUP BY every row is in one group — even when there are no rows at all.
- Each aggregate runs once per group: COUNT(*) counts rows, COUNT(col) counts non-NULL values, SUM / AVG / MIN / MAX skip NULLs and return NULL when nothing is left. AVG is always a REAL number.
- HAVING tests each group's aggregate; a group is kept only when the test is TRUE.
Default run (18 steps): SELECT artist, SUM(plays) FROM track GROUP BY artist HAVING SUM(plays) > 200 ORDER BY artist. Start with the 8 rows of track. … Result: 2 rows, ordered by artist (NULL first).
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One aggregate per query; groups are listed in key order (ORDER BY the group column, NULL first).
Loading the simulation…