PostgreSQL GROUP BY and Aggregate Functions
On this page
Building a SQL sales report
Sooner or later someone will ask you for "revenue by region, with subtotals, and a column for big deals". You can do all of it in one SQL query. This lesson builds that report one clause at a time, starting from a small sales table:
CREATE TABLE sales (
sale_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
region text NOT NULL,
salesperson text,
sale_date date NOT NULL,
amount numeric(10,2)
);
INSERT INTO sales (region, salesperson, sale_date, amount) VALUES
('West', 'Ana', '2026-01-10', 1200), ('West', 'Ana', '2026-02-03', 300),
('West', 'Ben', '2026-02-20', 800), ('East', 'Cy', '2026-01-15', 2500),
('East', 'Cy', '2026-03-01', NULL), ('East', NULL, '2026-03-09', 400);Notice the two NULLs: one sale has no amount yet, and one has no salesperson. They matter in a moment.
Aggregate functions: COUNT, SUM and AVG
Aggregate functions turn many rows into one value:
SELECT count(*), sum(amount), avg(amount), min(amount), max(amount)
FROM sales;
-- 6 | 5200.00 | 1040.00 | 300.00 | 2500.00Check the average. 5200 divided by 6 rows is 866.67, yet avg says 1040. Every aggregate except count(*) skips NULLs. So avg divided by the 5 known amounts, not by all 6 rows. That's usually what you want, but you should know it's happening.
COUNT has three forms, and they answer three different questions:
| Form | Counts | Here |
|---|---|---|
count(*) | Rows | 6 |
count(salesperson) | Rows where salesperson isn't NULL | 5 |
count(DISTINCT salesperson) | Different non-NULL salespeople | 3 |
All three are valid and all return a plausible number. Picking the wrong one is a silent bug, so ask yourself which question the report is really asking.
GROUP BY
GROUP BY puts rows with the same value into one group and runs the aggregates once per group:
SELECT region, count(*) AS sales, sum(amount) AS revenue
FROM sales
GROUP BY region
ORDER BY revenue DESC;
-- East | 3 | 2900.00
-- West | 3 | 2300.00Every column in the SELECT list must either be in GROUP BY or be inside an aggregate. Try adding salesperson without grouping by it and PostgreSQL refuses. Each region has several salespeople, so which one should it show? Some databases quietly pick one at random. PostgreSQL makes you choose. There's one exception: if you group by a table's primary key, you can select that table's other columns, because each group has only one row.
WHERE or HAVING?
Both filter, at different moments. WHERE filters rows before grouping. HAVING filters groups after the aggregates are computed, so only HAVING can use sum() or count().
SELECT region, sum(amount) AS revenue
FROM sales
WHERE sale_date >= '2026-02-01' -- which rows go into the groups
GROUP BY region
HAVING sum(amount) > 1000; -- which groups appear in the result
-- West | 1100.00If a condition can be checked on one row by itself, put it in WHERE. You'd get the same final answer with HAVING, but PostgreSQL would group and sum rows it was going to throw away.
Subtotals with ROLLUP, CUBE and GROUPING SETS
Finance wants revenue per salesperson, a subtotal per region and a grand total. You could write three queries and UNION them. ROLLUP does it in one pass:
SELECT region, salesperson, sum(amount) AS revenue,
GROUPING(salesperson) AS is_subtotal
FROM sales
GROUP BY ROLLUP (region, salesperson)
ORDER BY region NULLS LAST, salesperson NULLS LAST;ROLLUP (region, salesperson) produces groups at three levels: per region and salesperson, per region, and everything. On subtotal rows, the rolled-up columns show NULL.
That's ambiguous here, because one East sale has a real NULL salesperson. GROUPING(salesperson) settles it. It returns 1 on subtotal rows and 0 on normal groups, even when the group's value happens to be NULL.
Two relatives are worth knowing. CUBE (region, salesperson) gives every combination, including totals per salesperson across all regions. GROUPING SETS lets you list exactly the groupings you want.
Lists instead of numbers: string_agg and array_agg
Not every report wants a number per group. "Which salespeople are in each region?" wants a list. string_agg joins values into one string, and array_agg collects them into an array:
SELECT region,
string_agg(DISTINCT salesperson, ', ' ORDER BY salesperson) AS team,
array_agg(amount ORDER BY sale_date) AS amounts
FROM sales
GROUP BY region
ORDER BY region;
-- East | Cy | {2500.00,NULL,400.00}
-- West | Ana, Ben | {1200.00,300.00,800.00}Look at the two NULLs again. string_agg skipped the East sale with no salesperson, like every other aggregate. array_agg kept the missing amount as a NULL element, because an array can hold one. Put ORDER BY inside the parentheses whenever order matters, since the list order is otherwise not guaranteed. With DISTINCT, the ORDER BY must use the same expression, which is why it sorts by salesperson here.
Conditional aggregates with FILTER
The last request: per region, show all sales and big sales over 1,000 side by side. The FILTER clause attaches a condition to a single aggregate:
SELECT region,
count(*) AS all_sales,
count(*) FILTER (WHERE amount > 1000) AS big_sales,
sum(amount) FILTER (WHERE amount > 1000) AS big_revenue
FROM sales
GROUP BY region;
-- East | 3 | 1 | 2500.00
-- West | 3 | 1 | 1200.00You'll see older code doing the same with sum(CASE WHEN amount > 1000 THEN amount END). It works, but FILTER says what it means. FILTER works with any aggregate, and with ROLLUP too.
Check yourself
Both, possibly. count(last_login) skips users who never logged in, while count(*) counts every user. Decide which question the dashboard answers and pick the matching form.
The year goes in WHERE, because it's checked per row before grouping. The average goes in HAVING, because it only exists after grouping.
Not necessarily. ROLLUP marks subtotal rows with NULL in the rolled-up column, but your data may also contain real rows where the salesperson is NULL, and both look the same. Add GROUPING(salesperson) to the select list, which returns 1 only on genuine subtotal rows, and label them explicitly.
count(*) counts rows, so a customer with ten orders counts ten times. count(DISTINCT customer_id) counts each customer once. Choosing the wrong one is a common cause of inflated "customer" numbers in reports.
Use FILTER: sum(amount) FILTER (WHERE status = 'paid') and sum(amount) FILTER (WHERE status = 'refunded') in the same SELECT. Each aggregate only sees the rows its filter allows, so one scan produces both columns.