The one-sentence model

Closed, the cards read as a scannable overview. Open one for the worked detail. The closing sections stay open. Everything works without JavaScript.

GROUP BY sorts rows into buckets; each aggregate function boils one bucket down to one number. One output row per bucket — always. Every rule below falls out of that.

01 The bucket model Rows go into buckets; aggregates summarize each bucket; one row comes out per bucket.
What you'll knowThe single picture that predicts every GROUP BY behavior — including the errors.

Aggregation is a two-step machine:

GROUP BY dept makes one bucket per department. count(*), avg(salary), max(hired) each reach into a bucket and come back with one number. The output has exactly as many rows as there are buckets.

Two consequences worth saying out loud:

FactWhy
a group with zero matching rows never appearsno rows → no bucket → no output row (“don't print 0” questions are excusing you from a letters-table LEFT JOIN)
grouping by more columns = more, smaller bucketsGROUP BY dept, status buckets by the combination
02 The golden rule Every SELECT column must be grouped or aggregated — because “which row's value?” has no answer.
What you'll knowWhy the “must appear in the GROUP BY clause” error exists, and how to fix it by intent.

After bucketing, a column you didn't group by has many values per bucket. SQL refuses to guess which one you meant:

the error
ERROR: column "e.name" must appear in the GROUP BY
       clause or be used in an aggregate function

The fix depends on what you wanted the column for — same for the whole bucket? group by it. A summary? aggregate it. If neither feels right, see card 07: you probably didn't want to collapse at all.

Deeper menuThe full fix-by-intent menu (MIN/MAX trap, array_agg, PK functional dependency) lives in Compute Low, Filter High · card 06. This card is the recall version.

Postgres errors loudly here — that's a feature. Some databases silently pick a value, which is worse than any error.

03 WHERE vs HAVING WHERE filters rows before bucketing; HAVING filters buckets after. Aggregate in the condition → HAVING.
What you'll knowThe one tell that routes a condition to the right clause, every time.
What is the condition about?
an individual row
WHERE
Runs before bucketing — rows that fail never enter a bucket.
a bucket's result
HAVING
Runs after — whole buckets are kept or dropped.

Both at once, in one query — “orders from 2025, only departments with more than 10 of them”:

select dept, count(*)
from orders
where order_year = 2025          -- row-level: filters before bucketing
group by dept
having count(*) > 10;            -- bucket-level: filters after
The tellIf the condition contains an aggregate function, it must be HAVING — WHERE runs at step 2 of the clause clock, before aggregates are born. Same “value not ready yet” logic as the clause clock.
Efficiency pointA row condition works in HAVING if you also group by the column — but it's wasteful (you bucket rows you then throw away) and it reads as confusion. Row conditions go in WHERE.
04 The COUNT trio count(*) counts rows · count(col) skips NULLs · count(distinct col) counts unique values.
What you'll knowWhich COUNT answers which English question — the classic wrong answer is using the wrong one.
ExpressionCountsEnglish question it answers
count(*)rows in the bucket“how many orders?”
count(col)rows where col IS NOT NULL“how many orders have a coupon?”
count(distinct col)unique non-NULL values“how many customers placed orders?”

The classic wrong answer

“How many customers placed orders?”

count(*)
-- ✕ counts orders, not customers

Correct

One per customer, however many orders each placed:

count(distinct customer_id)
-- ✓ unique customers

The middle one — count(col) skipping NULLs — is also a tool, not just a trap: it's one way to count a subset (card 06).

05 Aggregates ignore NULLs AVG averages only what's there — and SUM of an empty bucket is NULL, not 0.
What you'll knowTwo quiet NULL behaviors: one usually right, one that bites in outer joins and empty results.

1 · AVG skips missing values

avg(score) divides by the count of non-NULL scores. Usually that's what you want — missing isn't zero. But if the question means “treat missing as 0,” that's a different query, and choosing it should be deliberate:

avg(score)                    -- average of those who have a score
avg(coalesce(score, 0))      -- average counting missing as zero — say why

2 · SUM over nothing is NULL

An aggregate over zero rows (an empty bucket via outer join, or a query matching nothing) returns NULL — except count, which returns 0. If downstream math expects a number:

coalesce(sum(amount), 0) as total   -- 0 when there was nothing to add
Same worldviewThis is the “NULL means unknown” rule from Compute Low, Filter High · card 05 — aggregates refuse to invent values for unknowns; they work with what's actually there.
06 Conditional aggregation: SUM(CASE) Count subsets side by side in one pass — built entirely from CASE, which you already own.
What you'll knowThe pattern behind every “show X and Y counts per group in one row” question — zero new syntax.

Read it as: for each row, CASE emits a 1 or a 0; SUM adds them up. Counting by summing ones.

the drill version — write this once by hand
select dept,
       count(*)                                            as total,
       sum(case when status = 'active' then 1 else 0 end) as active,
       sum(case when salary > 100000 then 1 else 0 end)   as high_paid
from employees
group by dept;

Swap SUM for other aggregates and the same trick totals a subset: sum(case when status = 'active' then salary else 0 end) is “total salary of active employees, per department.”

Recognize, don't writePostgres has a tailor-made form: count(*) filter (where status = 'active'). Same result. If you see FILTER in someone else's code, read it as a WHERE glued onto one aggregate. In the pad, write SUM(CASE) — it's the form your fingers know, and it works in every database.

Why interviewers love this pattern: it turns rows into columns (a mini-pivot) without any pivot machinery — and it answers “active vs inactive per department, side by side” in one pass over the table.

07 GROUP BY vs OVER — the first fork Collapse to one row per group, or keep every row with the group stat alongside?
What you'll knowThe first question to ask when reading any aggregation-flavored problem.
Does the answer keep the detail rows?
no — one row per group
GROUP BY
“average salary per department” — the departments are the answer.
yes — every row, plus a group stat
agg() OVER (PARTITION BY …)
“each employee and their department's average” — the rows are the answer.
-- collapse: one row per department
select dept, avg(salary) from employees group by dept;

-- keep: every employee, department average alongside
select name, salary,
       avg(salary) over (partition by dept) as dept_avg
from employees;

The second form is also the one-pass replacement for a correlated subquery — “paid above their department's average” is where salary > dept_avg on the kept rows, computed low and filtered high in a CTE.

Make it a reflexAsk this fork before typing. If reducing a column to one value per group feels wrong (card 02's error keeps appearing), that's the tell you wanted OVER, not GROUP BY.

08Common misconceptions

  • “count(*) and count(col) are the same.”
    count(col) silently skips NULLs. On a nullable column the two disagree — and the difference is exactly the rows with missing data.
  • “WHERE and HAVING are interchangeable.”
    WHERE filters rows before bucketing; HAVING filters buckets after. A condition containing an aggregate must be HAVING; a row condition belongs in WHERE.
  • “SUM over no rows returns 0.”
    It returns NULL — only count returns 0 on an empty set. Wrap in coalesce(sum(x), 0) when a number is expected downstream.
  • “A group with zero matches shows up with count 0.”
    No rows → no bucket → no output row. Zeros require manufacturing the full set of groups first (a dimension table or generate_series) and LEFT JOINing the counts on.
  • “Conditional counts need the FILTER clause.”
    sum(case when … then 1 else 0 end) does the same job in every database, from parts you already know. FILTER is a Postgres nicety — recognize it, don't depend on it.

09What good understanding looks like

  • You can state the bucket model in one sentence — and predict the output row count before running the query.
  • You route conditions by reflex: row condition → WHERE, aggregate condition → HAVING.
  • You hear “how many customers…” and reach for count(distinct customer_id), not count(*).
  • You write sum(case when … then 1 else 0 end) for side-by-side subset counts without pausing.
  • You ask “do I keep the detail rows?” before typing — GROUP BY to collapse, OVER to keep.

10Glossary

Aggregate function
A function that boils a set of rows down to one value — count, sum, avg, min, max, string_agg, array_agg.
Bucket (group)
The set of rows sharing one combination of GROUP BY values. Each bucket becomes exactly one output row.
HAVING
The bucket-level filter — runs after GROUP BY, so it can see aggregate results that WHERE cannot.
Conditional aggregation
Aggregating only the rows that meet a condition, inside a wider group — sum(case when … then 1 else 0 end), or Postgres's FILTER (WHERE …).
Golden rule
Every SELECT column must appear in the GROUP BY or inside an aggregate — because after collapsing, an ungrouped column has no single value to show.
PARTITION BY
The window-function counterpart of GROUP BY: same bucketing, but the rows are kept and the group stat rides alongside them.