Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 2 · Lesson 8 · 6 min read

PostgreSQL GROUP BY and Aggregate Functions

On this page
  1. Building a SQL sales report
  2. Aggregate functions: COUNT, SUM and AVG
  3. GROUP BY
  4. WHERE or HAVING?
  5. Subtotals with ROLLUP, CUBE and GROUPING SETS
  6. Lists instead of numbers: string_agg and array_agg
  7. Conditional aggregates with FILTER
  8. Check yourself

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:

SQL
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:

SQL
SELECT count(*), sum(amount), avg(amount), min(amount), max(amount)
FROM sales;
-- 6 | 5200.00 | 1040.00 | 300.00 | 2500.00

Check 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:

FormCountsHere
count(*)Rows6
count(salesperson)Rows where salesperson isn't NULL5
count(DISTINCT salesperson)Different non-NULL salespeople3

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:

SQL
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.00

Every 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().

SQL
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.00

If 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:

SQL
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:

SQL
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:

SQL
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.00

You'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.