← All notes

Summary

An aggregate folds many rows into one value: count, sum, avg, max, or min. GROUP BY repeats that once per group. WHERE picks rows before the fold. HAVING picks groups after it. FILTER limits one aggregate and leaves the others alone.

Where each clause runs

max(temp_lo) over a weather table returns one number. You cannot put that max in the WHERE of the same query, because WHERE chooses the rows that enter the aggregate and therefore runs first. To find which city produced the maximum, compute max in a subquery and compare the outer row to that separate result.

GROUP BY city with count(*) and max(temp_lo) yields one row per city. Each aggregate sees only the rows for that city. HAVING max(temp_lo) < 40 then drops groups. A test that needs no aggregate, such as a city name matched with LIKE, belongs in WHERE. Rows that fail WHERE are never grouped, which is cheaper. A HAVING clause with no aggregate is allowed but rarely worth it.

FILTER sits on one call. count(*) FILTER (WHERE temp_lo < 45) counts only those rows, while max in the same select still sees the whole group.

What comes back

count(*) counts input rows. count of an expression skips nulls. Except for count, these functions return null when no rows were selected, so a sum of no rows is null, not zero. coalesce substitutes a zero or an empty array. array_agg keeps nulls. string_agg concatenates non-null values with a delimiter. json_agg keeps nulls; the strict form skips them. json_object_agg rejects null keys, and the unique forms error on a repeated key. bool_and is true only if every non-null input is true. every is another name for it. any and some are not aggregates, because ANY already introduces a subquery.

Order is unspecified for array_agg, the json aggregates, and string_agg unless you put ORDER BY inside the call. count(*) of a whole table scans the table or a full index, so cost grows with the table. Percentiles and mode use WITHIN GROUP (ORDER BY ...), and a fraction must be between 0 and 1.

Claims

• id: C1 claim: An aggregate function computes one result from many input rows, including count, sum, average, max, and min. source: S1 evidence: "An aggregate function computes a single result from multiple input rows."
• id: C2 claim: An aggregate such as max cannot appear in the WHERE clause, because WHERE runs before aggregates are computed. source: S1 evidence: "this will not work since the aggregate max cannot be used in the WHERE clause."
• id: C3 claim: WHERE filters input rows before grouping, and HAVING filters group rows after aggregates have been computed. source: S1 evidence: "WHERE selects input rows before groups and aggregates are computed"
• id: C4 claim: FILTER drops rows only for the aggregate it is attached to, leaving other aggregates in the same query unchanged. source: S1 evidence: "it removes rows only from the input of the particular aggregate function that it is attached to."
• id: C5 claim: Aside from count, these aggregates return null when no rows are selected, so a sum of no rows is null rather than zero. source: S2 evidence: "except for count , these functions return a null value when no rows are selected."
• id: C6 claim: count applied to an entire table does work proportional to the table size, because PostgreSQL must scan the table or a full index. source: S2 evidence: "will require effort proportional to the size of the table"

@misc{iil_note_58g9yq88,
  author = {{iinventedlight}},
  title = {{Aggregates and GROUP BY in PostgreSQL}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-03},
  url = {https://iinventedlight.com/library/notes/postgres-aggregates-group-by/},
  note = {Permanent ID iil:note:58g9yq88. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-aggregates-group-by.md.