Tools per Shelf

An aggregate collapses rows

count(), sum(), avg(), min() and max() each read many rows and return one value. Used on their own, they collapse the whole table into a single row:

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, habitat TEXT, seen INTEGER);
INSERT INTO birds (species, habitat, seen) VALUES
  ('wren', 'hedge', 12),
  ('heron', 'water', 1),
  ('moorhen', 'water', 9),
  ('dunnock', 'hedge', 3),
  ('kingfisher', 'water', 2);

SELECT count(*) AS species, sum(seen) AS sightings, max(seen) AS most_seen FROM birds;

species | sightings | most_seen
--------+-----------+----------
      5 |        27 |        12
(1 row)

Five rows in, one row out. Notice that no column from the table appears in the result — there is nowhere to put five different species names.

GROUP BY gives one answer per pile

GROUP BY sorts the rows into piles by the value of a column, then runs the aggregates once per pile. You get one output row per pile, and the column you grouped by is the one thing it is safe to show alongside.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, habitat TEXT, seen INTEGER);
INSERT INTO birds (species, habitat, seen) VALUES
  ('wren', 'hedge', 12),
  ('heron', 'water', 1),
  ('moorhen', 'water', 9),
  ('dunnock', 'hedge', 3),
  ('kingfisher', 'water', 2);

SELECT habitat, count(*) AS species, sum(seen) AS sightings
FROM birds
GROUP BY habitat
ORDER BY habitat;

habitat | species | sightings
--------+---------+----------
hedge   |       2 |        15
water   |       3 |        12
(2 rows)

The rule that keeps these queries honest: every column in the SELECT list must either be grouped by or wrapped in an aggregate. Ask for a bare species next to count(*) and the question is meaningless, because the water pile holds three species and the row has room for one. SQLite will answer anyway, with a value from an arbitrary row; PostgreSQL refuses, which is friendlier.

HAVING filters the piles

WHERE throws rows away before the piles are built. HAVING throws whole piles away after, and it is the only place an aggregate can be tested.

CREATE TABLE birds (id INTEGER PRIMARY KEY, species TEXT, habitat TEXT, seen INTEGER);
INSERT INTO birds (species, habitat, seen) VALUES
  ('wren', 'hedge', 12),
  ('heron', 'water', 1),
  ('moorhen', 'water', 9),
  ('dunnock', 'hedge', 3),
  ('kingfisher', 'water', 2),
  ('nightjar', 'heath', 1);

SELECT habitat, count(*) AS species, sum(seen) AS sightings
FROM birds
WHERE seen > 1
GROUP BY habitat
HAVING count(*) >= 2
ORDER BY sightings DESC;

habitat | species | sightings
--------+---------+----------
hedge   |       2 |        15
water   |       2 |        11
(2 rows)

Read that in the order the database works: gather six rows, drop the two with a single sighting, build piles from the four that are left, drop any pile with fewer than two species, then sort. The heath pile never survived the WHERE, and the water pile went to the HAVING.

Try moving count(*) >= 2 into the WHERE clause and you get misuse of aggregate: count() — a clear error for once, and the reason the two clauses both exist.

Your turn

The editor holds a seven-row tools table and a query that is meant to be a per-shelf stocktake: how many tools and how much deposit each shelf holds, for shelves with two or more tools, biggest total first.

Press Run first. One row comes back, with a count of 7 and a total of 6300 against a single shelf name that SQLite picked from somewhere. That is the whole table collapsed into one pile.

Add the two missing clauses between FROM tools and ORDER BY, and run it again. Two rows should come back.

Then delete the HAVING line to see the two single-tool shelves reappear, and put it back.

If something goes wrong

If you get misuse of aggregate: count(), the group condition is in a WHERE and needs to be in a HAVING.

If you get near "GROUP": syntax error, the clauses are out of order — GROUP BY and HAVING both come before ORDER BY.

If you get seven rows, the grouping column is probably name rather than shelf, which makes one pile per tool.

If you get four rows, the HAVING is missing and the small shelves are still in.

Nothing here can break. The table is rebuilt from scratch on every run.