string_agg, FILTER & friends

Collapse groups into lists, and aggregate a subset inline.

Beyond count and sum

Postgres adds powerful aggregates:

string_agg(expr, sep ORDER BY ...) — concatenate group values into one string
array_agg(expr) — collect them into an array
count(*) FILTER (WHERE cond) — aggregate only the rows matching a condition, without a separate query
count(DISTINCT col) — count unique values

SELECT count(*) AS total,
       count(*) FILTER (WHERE stock = 0) AS sold_out
FROM books;

FILTER is the clean way to get several differently-filtered counts in one pass.

FILTER narrows one aggregate without narrowing the query, so a total and a subset total come back side by side. Two of the three fiction titles are out of stock; nothing else is.