Aggregate functions: COUNT, SUM, FILTER, and the traps

The three faces of COUNT

Choosing the wrong COUNT variant is the most common aggregation bug in reporting code — it produces a wrong number without an error. Here’s what each form actually does:

COUNT has three distinct behaviors that beginners conflate — and the difference changes the answer:

• COUNT(*) — counts rows, period. NULLs included; it never inspects column values.
• COUNT(col) — counts rows where col is not NULL. It silently skips NULLs.
• COUNT(DISTINCT col) — counts distinct non-NULL values of col.

SELECT
  COUNT(*)               AS total_orders,      -- every row
  COUNT(discount_code)   AS with_discount,     -- rows where code is not NULL
  COUNT(DISTINCT user_id) AS distinct_buyers   -- unique users
FROM orders;

If 1,000 orders exist but only 200 have a discount_code, COUNT(*) is 1000 and COUNT(discount_code) is 200. Reaching for COUNT(some_column) when you meant “all rows” is a top-5 reporting bug — a single NULL in that column silently undercounts.

FILTER: conditional aggregates without CASE

You often want “count of paid orders” and “count of cancelled orders” in the same row. The old trick is CASE inside the aggregate; the modern, clearer way is the FILTER clause:

SELECT
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'paid')    AS paid_revenue
FROM orders;

FILTER (WHERE …) restricts which rows that one aggregate sees, independently per aggregate, without touching the query’s WHERE. It reads cleaner than SUM(CASE WHEN status='paid' THEN total END) and — crucially — COUNT(*) FILTER (WHERE …) counts matching rows, whereas COUNT(CASE WHEN … THEN 1 END) works only because the non-matching branch returns NULL (a subtlety that bites people who write THEN 0, which counts everything). FILTER is standard SQL and is the senior default for conditional aggregation. (You’ll build full pivot tables with it in lesson 05.)

The performance angle is that all three FILTER aggregates above compute in one pass over the data. The alternative juniors reach for — three separate queries (WHERE status='paid', then WHERE status='cancelled', …) UNIONed or run sequentially — scans the table three times. On a 12M-row orders table that’s ~2.4s (3 × ~800ms Seq Scan) versus ~850ms for the single-scan FILTER form: the engine reads each row once and routes it to whichever aggregates’ predicates it satisfies. A separate trap is COUNT(DISTINCT col): unlike a plain COUNT, it can’t be a streaming counter — it must materialize and dedupe every value, so on a high-cardinality column it builds a sort or hash of all distinct values and is the part of the query that spills to disk first. COUNT(*) FILTER stays a cheap counter; COUNT(DISTINCT …) is the expensive cousin to watch in EXPLAIN ANALYZE.

Ordered aggregates: string_agg / array_agg

Some aggregates care about order. string_agg and array_agg concatenate the group’s values, and you can specify the order inside the aggregate:

SELECT user_id,
       string_agg(status, ',' ORDER BY created_at) AS status_timeline,
       array_agg(id ORDER BY created_at DESC)      AS recent_first
FROM orders
GROUP BY user_id;

Without ORDER BY inside the aggregate, the concatenation order is undefined — it depends on the plan and can change between runs. If order matters (a timeline, a CSV), always specify it inside the parentheses, not in the query’s outer ORDER BY (which only sorts the final rows, not the values inside each aggregate).

The numeric traps

Two aggregate bugs are silent and expensive:

Integer AVG. AVG of an integer column returns numeric in Postgres (good), but SUM/division you write by hand on integers truncates. The classic is SUM(total)/COUNT(*) on int columns — that’s integer division, dropping the fraction. Cast first: SUM(total)::numeric / COUNT(*) or just use AVG(total).

SUM overflow. SUM over an integer column returns bigint (safe to ~9.2×10^18), but SUM over a bigint column returns numeric — and SUM over a column that’s int with billions of large rows can still overflow if you cast back. The real-world failure: summing a money column stored as int cents across a busy table eventually trips integer out of range. Defend by storing money as numeric (or bigint cents) and, when in doubt, cast inside the SUM: SUM(amount::bigint).