Aggregates & GROUP BY
FILTER, HAVING, and grouped aggregates. Practical PostgreSQL patterns for production queries.
//
Search across all documentation pages
FILTER, HAVING, and grouped aggregates. Practical PostgreSQL patterns for production queries.
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.
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:
| 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 |
Use multiline template strings or SQL files; lint in CI.
When you need unique rows without aggregates.
Sort can use index order if query matches index columns.
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+.
Reviewed by Chris St. John·Last updated Jul 19, 2026