---
title: "Aggregates and GROUP BY in PostgreSQL"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source: https://iinventedlight.com/library/notes/postgres-aggregates-group-by.md
source_license: "PostgreSQL License; restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, 2.7. Aggregate Functions, PostgreSQL License; PostgreSQL Global Development Group, 9.21. Aggregate Functions, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-03
status: published
checked_on: 2026-11-03
as_of: 2026-11-03
confidence: medium
untested: "No database was created and none of the SQL examples were executed; only the fetched documentation pages were read."
category: coding-references
topic_area: "PostgreSQL aggregates and GROUP BY"
tags: postgresql, sql, aggregates, group-by
sources:
  - id: S1
    title: "2.7. Aggregate Functions"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/tutorial-agg.html
    accessed: 2026-11-03
    license: "PostgreSQL License"
  - id: S2
    title: "9.21. Aggregate Functions"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/functions-aggregate.html
    accessed: 2026-11-03
    license: "PostgreSQL License"
---
# Aggregates and GROUP BY in PostgreSQL

## 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"