PostgreSQL HAVING Clause: Filtering Groups with GROUP BY
Master the postgresql having clause — filter grouped rows with HAVING, contrast WHERE vs HAVING, and use FILTER, string_agg, and ROLLUP in real queries.
WHERE filters rows; the postgresql having clause filters *groups*. That one-sentence distinction unlocks every analytics query you'll ever write: top customers by revenue, duplicate records, categories above a threshold. This guide builds from a self-contained orders table to real aggregation patterns — HAVING with SUM and COUNT , the FILTER clause, string_agg , and the "column must appear in the GROUP BY clause" error fix. What you'll learn GROUP BY + aggregate functions: the analytics foundation The HAVING clause: filtering groups after aggregation WHERE vs HAVING: which runs when, and why it matters FILTER (WHERE ...) for conditional aggregates in one pass string_agg for collapsing grouped text into lists Fixing "column must appear in the GROUP BY clause" (error 42803) Step 1 — Set up the orders table Every example below runs against this table — create it and load the data: CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer TEXT NOT NULL, region TEXT NOT NULL, amount NUMERIC(10, 2) NOT NULL, ordered_at DATE NOT NULL ); INSERT INTO orders (customer, region, amount, ordered_at) VALUES ('Ayesha', 'Punjab', 120.00, '2026-01-05'), ('Bilal', 'Sindh', 75.50, '2026-01-06'), ('Ayesha', 'Punjab', 200.00, '2026-02-10'), ('Danish', 'Punjab', 40.00, '2026-02-11'), ('Bilal', 'Sindh', 300.00, '2026-03-01'), ('Ayesha', 'Punjab', 50.00, '2026-03-02'); Step 2 — GROUP BY and aggregate functions GROUP BY collapses rows sharing a value into one row per group, and aggregate functions compute over each group: SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total_spent FROM orders GROUP BY…