Aggregations, GROUP BY, and HAVING in PostgreSQL

Aggregation is how you turn a table full of rows into a number a human actually wants: total revenue, orders per customer, average response time. The mechanics are simple to start with and have a few sharp edges that produce wrong answers quietly, which is the worst kind of wrong. This article walks the whole path, from a basic count to the grouping-set tricks that replace three separate queries with one.

This is part 6 of the PostgreSQL Masterclass, following joins.

Aggregate functions over the whole table

Without a GROUP BY, an aggregate collapses the entire result into a single row:

SELECT
  count(*)        AS order_count,
  sum(total)      AS revenue,
  avg(total)      AS avg_order,
  min(created_at) AS first_order,
  max(created_at) AS last_order
FROM orders;

One thing to internalize early: aggregates skip NULL. count(total) counts rows where total is not null, while count(*) counts all rows. avg(total) divides by the count of non-null values, not the row count. This is usually what you want, and it's occasionally a surprise, so know which one you're using.

GROUP BY: one row per group

GROUP BY splits the rows into groups and runs the aggregate per group:

SELECT customer_id, count(*) AS order_count, sum(total) AS revenue
FROM orders
GROUP BY customer_id;

The rule that trips people up: every column in the SELECT list must either be inside an aggregate or listed in GROUP BY. Postgres enforces this and gives a clear error if you break it, because a column that's neither grouped nor aggregated has no single value per group. If you want the customer's name alongside the totals, group by it too, or join to it after aggregating.

WHERE vs HAVING

This distinction confuses people, and it's actually simple once you see the timing. WHERE filters rows before grouping. HAVING filters groups after aggregating. You can't use an aggregate in WHERE, because the aggregate doesn't exist yet at that stage.

-- customers whose completed orders total more than 1000
SELECT customer_id, sum(total) AS revenue
FROM orders
WHERE status = 'completed'      -- filter rows first
GROUP BY customer_id
HAVING sum(total) > 1000;       -- filter groups after

WHERE status = 'completed' throws out individual orders before they're grouped. HAVING sum(total) > 1000 throws out whole customers whose grouped total is too low. Putting status in HAVING would be wrong, and putting the sum in WHERE is an error. When you filter, ask whether you're filtering rows or groups, and that tells you which clause.

FILTER: conditional aggregates without a mess

A common need is counting or summing different subsets in the same query, like completed versus refunded revenue side by side. The clean way in Postgres is the FILTER clause:

SELECT
  customer_id,
  count(*) AS total_orders,
  count(*) FILTER (WHERE status = 'completed') AS completed_orders,
  sum(total) FILTER (WHERE status = 'completed') AS completed_revenue,
  sum(total) FILTER (WHERE status = 'refunded')  AS refunded_revenue
FROM orders
GROUP BY customer_id;

Before FILTER existed, people wrote sum(CASE WHEN status = 'completed' THEN total ELSE 0 END). That still works, but FILTER says what you mean more directly and handles nulls correctly without the ELSE 0 fiddliness. Reach for it any time you want an aggregate over a subset of the group.

string_agg and array_agg: collapsing rows into one value

Sometimes you want the grouped rows themselves, not a count. string_agg joins them into a delimited string, and array_agg collects them into an array:

SELECT
  o.customer_id,
  string_agg(o.id::text, ', ' ORDER BY o.created_at) AS order_ids,
  array_agg(o.total ORDER BY o.total DESC)           AS totals_desc
FROM orders o
GROUP BY o.customer_id;

The ORDER BY inside the aggregate controls the order of the collected values, which matters when the output order is meaningful. array_agg pairs especially well with jsonb and array columns, and we'll use it again later in the series.

GROUPING SETS, ROLLUP, and CUBE

When you need subtotals and a grand total in the same result, the naive approach is several queries stitched together with UNION ALL. Postgres does it in one pass with grouping sets:

SELECT
  region,
  product,
  sum(total) AS revenue
FROM sales
GROUP BY ROLLUP (region, product);

ROLLUP (region, product) produces revenue per region-and-product, then a subtotal per region, then a grand total, all in one result set. The subtotal rows have NULL in the rolled-up column, which you can label with GROUPING() if you want readable output. CUBE gives every combination of the columns, and GROUPING SETS lets you list exactly the groupings you want. For a reporting query, these turn a page of SQL into a few lines.

The one that produces wrong numbers

Back to the trap from the joins article, because it shows up most often with aggregates. If you join to a one-to-many table and then aggregate a value from the one side, you count it once per joined row:

-- WRONG: customer credit summed once per order row
SELECT c.id, sum(c.account_credit)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Whenever you GROUP BY after a join, ask which table each aggregated column comes from. If it's on the "one" side of a one-to-many join, the join has already duplicated it, and your sum is inflated. Aggregate the "many" side in a subquery first, then join.

Aggregation is one of the highest-value parts of SQL, because pushing summarization into the database is far faster than pulling thousands of rows into application code and looping. Learn GROUP BY, HAVING, FILTER, and the grouping sets, and a surprising share of your "I'll just do this in code" moments turn back into a single query.

Next, we go one level deeper into analytics with window functions, which compute across rows without collapsing them the way GROUP BY does.