Aggregates & GROUP BY

FILTER, HAVING, and grouped aggregates. Practical PostgreSQL patterns for production queries.

Recipe

SELECT customer_id,
       count(*) AS orders,
       sum(total) AS revenue,
       avg(total) FILTER (WHERE total > 100) AS avg_big_orders
FROM app.orders
GROUP BY customer_id
HAVING sum(total) > 500;

When to reach for this: This pattern appears in application or reporting SQL you maintain.

Working Example

SELECT date_trunc('day', created_at) AS day,
       count(*) FILTER (WHERE email LIKE '%@example.com') AS corp_signups,
       count(*) AS all_signups
FROM app.customers
GROUP BY 1
ORDER BY 1 DESC;

What this demonstrates:

FILTER clause counts subsets without CASE
HAVING filters groups after aggregation
date_trunc groups timestamptz by day

Deep Dive

Rules

SELECT list non-aggregated columns must appear in GROUP BY (or be functionally dependent on PK in PG 9.1+).
WHERE filters rows before grouping; HAVING filters groups.
FILTER is cleaner than SUM(CASE WHEN ...).

Gotchas

SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.
NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.
Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.
Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.
Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternatives

Alternative	Use When	Don't Use When
ORM query builder	Team standardizes on one stack	Complex SQL becomes opaque
Materialized view	Repeat expensive aggregates	Needs refresh strategy
Warehouse replica	Heavy BI scans	Not for OLTP latency

FAQs

Should I format SQL in application strings?

Use multiline template strings or SQL files; lint in CI.

When is DISTINCT enough?

When you need unique rows without aggregates.

Does ORDER BY hurt indexes?

Sort can use index order if query matches index columns.

Subqueries & EXISTS - semi-join patterns
Window Functions - non-collapsing aggregates

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

SELECT customer_id, count(*) AS orders, sum(total) AS revenue, avg(total) FILTER (WHERE total > 100) AS avg_big_orders FROM app.orders GROUP BY customer_id HAVING sum(total) > 500;

Working Example

SELECT date_trunc('day', created_at) AS day,
       count(*) FILTER (WHERE email LIKE '%@example.com') AS corp_signups,
       count(*) AS all_signups
FROM app.customers
GROUP BY 1
ORDER BY 1 DESC;

What this demonstrates:

FILTER clause counts subsets without CASE

HAVING filters groups after aggregation

date_trunc groups timestamptz by day

Gotchas

SELECT * in apps - New columns break serializers.. Fix: List columns explicitly.

NULL comparisons - = NULL is always unknown.. Fix: Use IS NULL or IS DISTINCT FROM.

Implicit casts - Surprise seq scans on mismatched types.. Fix: Match predicate types to column types.

Unbounded queries - Missing LIMIT on large tables.. Fix: Paginate or aggregate intentionally.

Stale statistics - Bad plans after bulk load.. Fix: Run ANALYZE on affected tables.

Alternative

Use When

Don't Use When

ORM query builder

Team standardizes on one stack

Complex SQL becomes opaque

Materialized view

Repeat expensive aggregates

Needs refresh strategy

Warehouse replica

Heavy BI scans

Not for OLTP latency

Aggregates & GROUP BY

Buscar en la documentación

Aggregates & GROUP BY