Aggregation & GROUP BY

COUNT variants, SUM over empty/all-NULL, WHERE vs HAVING, non-aggregated columns, NULL groups.

• ★COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

  COUNT(*) counts rows, COUNT(col) counts non-NULLs, COUNT(DISTINCT col) counts unique values — three different questions.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE ballots(voter INT, choice VARCHAR); INSERT INTO ballots VALUES (1,'A'),(2,NULL),(3,'A'),(4,'B');

  The trap

  SELECT COUNT(choice) AS n FROM ballots;

  3

  The fix

  SELECT COUNT(*) AS n FROM ballots;

  4

  The 'ballots cast' total reads 3, not 4 — COUNT(choice) silently skipped the blank ballot; COUNT(*) counts the row regardless.

• SUM over an empty / all-NULL group

  SUM of no rows (or all-NULL) returns NULL, not 0 — COALESCE(SUM(...),0) at the bar.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE artists(artist VARCHAR); INSERT INTO artists VALUES ('Mo'),('Vee'); CREATE TABLE sessions(artist VARCHAR, minutes INT); INSERT INTO sessions VALUES ('Mo',90),('Mo',45);

  The trap

  SELECT a.artist, SUM(s.minutes) AS total FROM artists a LEFT JOIN sessions s ON s.artist = a.artist GROUP BY a.artist ORDER BY a.artist;

  Mo	135
  Vee	NULL

  The fix

  SELECT a.artist, COALESCE(SUM(s.minutes), 0) AS total FROM artists a LEFT JOIN sessions s ON s.artist = a.artist GROUP BY a.artist ORDER BY a.artist;

  Mo	135
  Vee	0

  The invoice run reads NULL for the new artist instead of 0 minutes — SUM over zero rows returns NULL, and downstream the payroll line blanks out.

• WHERE vs HAVING

  WHERE filters rows before grouping; HAVING filters groups after — a misplaced HAVING filters the wrong thing.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE rides(pilot VARCHAR, passengers INT); INSERT INTO rides VALUES ('ana',4),('ana',2),('bo',1);

  The trap

  SELECT pilot, SUM(passengers) FROM rides WHERE SUM(passengers) > 3 GROUP BY pilot ORDER BY pilot;

  0 rows

  The fix

  SELECT pilot, SUM(passengers) AS total FROM rides GROUP BY pilot HAVING SUM(passengers) > 3 ORDER BY pilot;

  ana

  6

  Filtering SUM(passengers) in WHERE raised a binder error — at WHERE time the groups don't exist yet, so an aggregate is unaskable; it belongs in HAVING.

• Non-aggregated column in SELECT

  Selecting a column that isn't grouped or aggregated errors in strict engines, returns silent garbage in loose ones.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE looms(mill VARCHAR, wool VARCHAR, kg INT); INSERT INTO looms VALUES ('north','merino',5),('north','alpaca',3),('south','merino',4);

  The trap

  SELECT mill, wool, SUM(kg) FROM looms GROUP BY mill ORDER BY mill;

  0 rows

  The fix

  SELECT mill, MAX(wool) AS heaviest, SUM(kg) AS kg FROM looms GROUP BY mill ORDER BY mill;

  north	merino	8
  south	merino	4

  Selecting the ungrouped 'wool' column raised a binder error — after grouping, one row stands for many looms, so a bare column has no single value; strict engines refuse, loose ones return silent garbage.

• NULLs form their own group

  GROUP BY collects all NULL keys into a single group — not skipped.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE coats(tag VARCHAR); INSERT INTO coats VALUES ('red'),(NULL),(NULL);

  The trap

  SELECT tag, COUNT(*) AS n FROM coats GROUP BY tag ORDER BY tag NULLS LAST;

  red	1
  NULL	2

  The fix

  SELECT COALESCE(tag, '<untagged>') AS tag, COUNT(*) AS n FROM coats GROUP BY tag ORDER BY tag NULLS LAST;

  <untagged>	2
  red	1

  The two untagged coats didn't vanish — GROUP BY collected them into one NULL group of 2, which renders blank and masquerades as a category; COALESCE labels it with a value that can't collide.

• Integer division inside aggregates

  SUM(a)/SUM(b) or AVG on integers truncates unless cast to a float first (dialect-dependent).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE r(hits INT, views INT); INSERT INTO r VALUES (3,7),(4,7);

  The trap

  SELECT SUM(hits) / SUM(views) AS rate FROM r;

  0.5

  The fix

  SELECT 1.0 * SUM(hits) / SUM(views) AS rate FROM r;

  0.5

  In DuckDB SUM(hits)/SUM(views) float-divides to 0.5, but on Postgres/SQL Server the same integer division truncates to 0 — every rate under 100% reads as 'no conversions'; casting one side (1.0 *) is correct on every engine.

• Average of averages

  An average of group averages weights every group equally regardless of size — it is not the overall average, and nothing errors.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE waits(stop VARCHAR, mins INT); INSERT INTO waits VALUES ('a',10),('a',10),('a',10),('b',40);

  The trap

  SELECT ROUND(AVG(stop_avg), 2) AS wait FROM (SELECT stop, AVG(mins) AS stop_avg FROM waits GROUP BY stop);

  25

  The fix

  SELECT ROUND(AVG(mins), 2) AS wait FROM waits;

  17.5

  The average wait read 25.0, not 17.5 — averaging each stop's average weighted a 3-visit stop and a 1-visit stop equally, so the busy stop's real waits were undercounted.

• STRING_AGG without ORDER BY

  STRING_AGG (and array_agg) without an internal ORDER BY concatenate in arbitrary order — the output silently encodes scan order.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE floats(f VARCHAR); INSERT INTO floats VALUES ('a'),('b'),('c');

  The trap

  SELECT string_agg(f, ' -> ') AS lineup FROM floats;

  0 rows

  The fix

  SELECT string_agg(f, ' -> ' ORDER BY f) AS lineup FROM floats;

  a -> b -> c

  The parade lineup banner flickered between refreshes though nothing wrote — string_agg without an internal ORDER BY concatenates in whatever order the plan produced, so the same rows yield different strings; string_agg(f, ... ORDER BY f) is stable.