Home›Postgres›Postgres – Aggregation and GROUP BY

Postgres – Aggregation and GROUP BY

April 26, 20196 min readUpdated 8/23/2026

Aggregation collapses many rows into one. The syntax is small; what trips people up is which columns are allowed in the SELECT list, where a filter belongs, and what count() does with NULL.

The aggregates

SELECT count(*)                        AS bookings,
       sum(total)                      AS revenue,
       round(avg(total), 2)            AS average,
       min(check_in)                   AS earliest,
       max(check_in)                   AS latest
FROM   bookings
WHERE  status = 'CONFIRMED';

With no GROUP BY, the whole table is one group and you get exactly one row — even if the table is empty, in which case count(*) is 0 and every other aggregate is NULL.

count(*) is not count(column)

SELECT count(*)                  AS rows,
       count(cancelled_at)       AS actually_cancelled,
       count(DISTINCT property_id) AS distinct_properties
FROM   bookings;
  • count(*) counts rows.
  • count(column) counts rows where that column is not NULL. This is genuinely useful — the second column above is a count of cancellations — and it is also the reason a count you expected to match does not.
  • count(DISTINCT column) counts distinct non-NULL values, and is much more expensive than the other two.

Every aggregate except count(*) ignores NULL. avg() over a column where half the rows are NULL divides by the half that are not, which is usually right and occasionally very wrong.

GROUP BY

SELECT status,
       count(*)               AS bookings,
       round(avg(nights), 2)  AS avg_nights
FROM   bookings
GROUP  BY status
ORDER  BY bookings DESC;
  status   | bookings | avg_nights
-----------+----------+------------
 COMPLETED |   200612 |       3.49
 CONFIRMED |   152854 |       3.52
 PENDING   |    25482 |       3.52
 CANCELLED |    21052 |       3.50

The rule: every column in the SELECT list must either be in the GROUP BY or be inside an aggregate. Postgres enforces it, which is a mercy — MySQL historically did not and would return an arbitrary row's value for the ungrouped column.

There is one exception, and it is a good one. Group by a primary key and Postgres knows every other column of that table is determined by it, so you may select them freely:

SELECT p.id, p.title, p.city, count(b.id) AS stays
FROM   properties p
LEFT   JOIN bookings b ON b.property_id = p.id
GROUP  BY p.id                      -- title and city come along, because id is the PK
ORDER  BY stays DESC
LIMIT  5;

WHERE, then GROUP BY, then HAVING

SELECT p.city, count(*) AS stays, round(avg(b.total), 2) AS avg_total
FROM   bookings b
JOIN   properties p ON p.id = b.property_id
WHERE  b.status = 'CONFIRMED'          -- filters ROWS, before grouping
GROUP  BY p.city
HAVING count(*) > 5000                 -- filters GROUPS, after
ORDER  BY stays DESC;

The difference is not stylistic. WHERE runs first and removes rows before the aggregation does any work; HAVING runs after and can only test aggregate results. A condition that could go in either belongs in WHERE, because it makes the grouping smaller.

The full order Postgres evaluates in, which explains most "column does not exist" errors:

FROM / JOIN  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY  ->  LIMIT

SELECT is nearly last, which is why an alias defined there cannot be used in WHERE — it does not exist yet — but can be used in ORDER BY, which comes after.

FILTER: several counts in one pass

The alternative to running the same query four times with different WHERE clauses:

SELECT p.city,
       count(*)                                        AS all_stays,
       count(*) FILTER (WHERE b.status = 'CONFIRMED')  AS confirmed,
       count(*) FILTER (WHERE b.status = 'CANCELLED')  AS cancelled,
       sum(b.total) FILTER (WHERE b.status <> 'CANCELLED') AS revenue
FROM   bookings b
JOIN   properties p ON p.id = b.property_id
GROUP  BY p.city
ORDER  BY all_stays DESC
LIMIT  5;

One scan, four answers. You will also see this written as sum(CASE WHEN … THEN 1 ELSE 0 END), which works and predates FILTER; FILTER is standard SQL, reads better, and handles NULL correctly without thinking about it.

Collecting values instead of counting them

SELECT p.city,
       count(*)                                   AS properties,
       string_agg(DISTINCT p.property_type, ', ' ORDER BY p.property_type) AS types
FROM   properties p
WHERE  p.status = 'PUBLISHED'
GROUP  BY p.city
ORDER  BY properties DESC
LIMIT  5;

string_agg joins values with a separator; array_agg gives you an array; json_agg and jsonb_agg give you JSON, which is how you return a parent and its children in a single round trip instead of an N+1 query. All four take an ORDER BY inside the parentheses, and without one the order is undefined.

Medians and percentiles

There is no median(), because a median needs the values sorted and an ordinary aggregate does not sort. The ordered-set aggregates do, and they take their ORDER BY in a WITHIN GROUP clause:

SELECT round(avg(total), 2)                                              AS mean,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY total)::numeric(10,2) AS median,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY total)::numeric(10,2) AS p95,
       mode() WITHIN GROUP (ORDER BY nights)                             AS most_common_nights
FROM   bookings
WHERE  status = 'CONFIRMED';

Report the median rather than the mean whenever a few large values would drag the average somewhere no real row sits — which is true of nearly every price, duration and response time you will ever aggregate. percentile_cont interpolates between rows; percentile_disc returns an actual value from the data. For money, the second one is usually what you meant.

Subtotals without a second query

SELECT p.country, p.property_type, count(*) AS stays
FROM   bookings b
JOIN   properties p ON p.id = b.property_id
WHERE  p.country IN ('Norway', 'Portugal')
GROUP  BY ROLLUP (p.country, p.property_type)
ORDER  BY p.country NULLS LAST, p.property_type NULLS LAST;

ROLLUP adds a subtotal row per country and a grand total, with NULL in the columns being totalled over. CUBE gives every combination; GROUPING SETS lets you name exactly the ones you want. Use GROUPING(column) to tell a genuine NULL from a subtotal marker.

Grouping is a sort or a hash, and it matters which

Postgres has two ways to group. HashAggregate builds a hash table of groups in memory and is the faster one; GroupAggregate sorts the rows first and reads the groups off in order. It picks the sort when the groups will not fit in work_mem, or when the input arrives sorted already.

Two consequences worth knowing before the EXPLAIN post:

  • Group by fewer, narrower columns where you have the choice. Grouping by a city name builds a hash table of sixteen entries; grouping by a UUID builds one entry per row, and then it does not fit.
  • An index on the grouping column can remove the sort entirely, because the rows already arrive in group order. That is why a GROUP BY status and a GROUP BY public_id over the same table behave nothing alike.

The count(*) that is slower than you expect

SELECT count(*) FROM bookings;

Because of MVCC, Postgres cannot answer that from a counter — it has to check which row versions are visible to your transaction, which means reading the table or a whole index. On 400,000 rows that is milliseconds. On a hundred million it is not, and a "total results" count on a paginated screen becomes the slowest thing on the page.

When an estimate will do, the planner already has one:

SELECT reltuples::bigint AS estimated_rows
FROM   pg_class
WHERE  relname = 'bookings';

It is only as fresh as the last ANALYZE, which is exactly the trade you are making when the alternative is a full scan on every page load. For a search screen, "about 12,000 results" is what the user wanted anyway, and it costs nothing.