So far every query returned individual rows. Reports, dashboards and APIs mostly need summaries: how many notes per user, activity per month, the newest note of each user. This lesson covers the three tools for that: aggregates with GROUP BY, common table expressions, and window functions.
Aggregate functions
An aggregate turns many rows into one value:
SELECT count(*) AS notes,
count(*) FILTER (WHERE pinned) AS pinned,
min(created_at)::date AS first,
max(created_at)::date AS last
FROM notes;
notes | pinned | first | last
-------+--------+------------+------------
5 | 2 | 2026-10-01 | 2026-11-02
(1 row)
| Function | Returns |
|---|---|
count(*) | Number of rows |
count(col) | Number of non-NULL values |
count(DISTINCT col) | Number of different non-NULL values |
sum, avg, min, max | What the name says; they ignore NULLs |
string_agg(col, ', ') | Values joined into one string |
array_agg(col) / jsonb_agg(col) | Values collected into an array or JSON array |
bool_and, bool_or | Whether all, or any, values are true |
FILTER (WHERE ...) restricts one aggregate to some rows. It replaces the old trick of sum(CASE WHEN ... THEN 1 ELSE 0 END) and lets one query compute many counts in a single pass over the table.
GROUP BY
GROUP BY splits the rows into groups and computes the aggregates once per group:
SELECT plan, count(*) AS users
FROM users
GROUP BY plan
ORDER BY plan;
plan | users
------+-------
free | 1
pro | 2
(2 rows)
Enum values sort in the order they were declared, so free comes before pro. Every column in the SELECT list must either be in GROUP BY or inside an aggregate; grouping by a table’s primary key lets you select any other column of that table.
Grouping by an expression works too. Notes per month:
SELECT date_trunc('month', created_at)::date AS month, count(*) AS notes
FROM notes
GROUP BY 1
ORDER BY 1;
month | notes
------------+-------
2026-10-01 | 4
2026-11-01 | 1
(2 rows)
GROUP BY 1 refers to the first column of the select list, a convenient shorthand for long expressions.
HAVING
WHERE filters rows before grouping; HAVING filters groups after it:
SELECT u.name, count(*) AS notes
FROM notes n
JOIN users u ON u.id = n.user_id
GROUP BY u.id, u.name
HAVING count(*) > 2;
name | notes
-----------+-------
Ana Silva | 3
(1 row)
ROLLUP, CUBE and GROUPING SETS add subtotal and total rows in the same query, for example GROUP BY ROLLUP (plan) adds a grand total row with plan NULL.
Common table expressions (WITH)
A long query is easier to read as a sequence of named steps. WITH defines temporary result sets, called CTEs, that the rest of the query uses like tables:
WITH per_user AS (
SELECT u.id, count(n.id) AS notes
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
GROUP BY u.id
)
SELECT round(avg(notes), 2) AS avg_notes_per_user,
max(notes) AS most_notes
FROM per_user;
avg_notes_per_user | most_notes
--------------------+------------
1.67 | 3
(1 row)
Postgres usually folds simple CTEs into the main query, so they cost nothing extra. Add MATERIALIZED (WITH per_user AS MATERIALIZED (...)) when you want a CTE computed exactly once.
CTEs can also change data. This moves old notes into an archive table in one atomic statement:
WITH moved AS (
DELETE FROM notes WHERE created_at < '2025-01-01' RETURNING *
)
INSERT INTO notes_archive SELECT * FROM moved;
Recursive CTEs
WITH RECURSIVE repeats a query, feeding each step’s result into the next, until no new rows appear. It walks trees such as folders, categories or org charts. A simple example that generates the numbers 1 to 5 and adds them up:
WITH RECURSIVE n(i) AS (
SELECT 1
UNION ALL
SELECT i + 1 FROM n WHERE i < 5
)
SELECT sum(i) FROM n;
sum
-----
15
(1 row)
For a folder tree, the first part selects the root folders (WHERE parent_id IS NULL) and the recursive part joins folders to the CTE on folders.parent_id = tree.id, carrying a depth or path column along.
Window functions
Aggregates collapse rows. Window functions compute a value across a set of related rows but keep every row. The set is defined with OVER (...):
PARTITION BYsplits rows into independent groups (likeGROUP BY, but without collapsing).ORDER BYinsideOVERorders rows within each partition.
Number each user’s notes from newest to oldest:
SELECT user_id, id, title,
row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS nth
FROM notes
ORDER BY user_id, nth;
user_id | id | title | nth
---------+----+------------------+-----
1 | 5 | Meeting notes | 1
1 | 2 | Deploy checklist | 2
1 | 1 | Shopping list | 3
2 | 4 | Postgres tips | 1
2 | 3 | Book ideas | 2
(5 rows)
Filter on nth = 1 in an outer query and you have the latest note per user, another solution to the problem LATERAL solved in lesson 8.
Comparing with the previous row
lag() and lead() read a value from the previous or next row. How much time passed between consecutive notes?
SELECT id, created_at - lag(created_at) OVER (ORDER BY created_at) AS since_previous
FROM notes
ORDER BY created_at;
id | since_previous
----+------------------
1 | (null)
2 | 2 days 05:30:00
3 | 2 days 05:45:00
4 | 6 days 12:30:00
5 | 21 days 01:15:00
(5 rows)
Running totals and shares
With ORDER BY inside OVER, an aggregate becomes cumulative. Without anything inside OVER (), it covers all rows, which is perfect for percentages:
SELECT user_id,
count(*) AS notes,
round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM notes
GROUP BY user_id
ORDER BY user_id;
user_id | notes | pct
---------+-------+------
1 | 3 | 60.0
2 | 2 | 40.0
(2 rows)
Here the window function runs after grouping: sum(count(*)) OVER () adds up the per-user counts.
| Window function | Use |
|---|---|
row_number() | 1, 2, 3 within the partition |
rank() / dense_rank() | Rankings with ties (with or without gaps) |
lag(col) / lead(col) | Previous or next row’s value |
first_value(col) / last_value(col) | First or last value in the window |
sum(col) OVER (ORDER BY ...) | Running total |
avg(col) OVER (ORDER BY ... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | Moving average over 7 rows |
ntile(4) | Split into quartiles |
Quick reference
| Task | SQL |
|---|---|
| Totals | SELECT count(*), sum(x) FROM t |
| Conditional count | count(*) FILTER (WHERE cond) |
| Per group | GROUP BY col |
| Filter groups | HAVING count(*) > n |
| Subtotals | GROUP BY ROLLUP (col) |
| Named steps | WITH a AS (...), b AS (...) SELECT ... |
| Trees | WITH RECURSIVE |
| Number rows per group | row_number() OVER (PARTITION BY g ORDER BY t DESC) |
| Previous row | lag(col) OVER (ORDER BY t) |
| Share of total | x / sum(x) OVER () |
Exercise
Which query counts each user’s notes and the pinned ones in a single pass?
Show answer
Answer: D. FILTER limits one aggregate to matching rows while the others still see the whole group. count(pinned) counts non-NULL values, which includes false.
Exercise
What is the difference between WHERE and HAVING?
Show answer
Answer: B. Aggregates such as count(*) do not exist yet when WHERE runs, so conditions on them must go in HAVING.
Exercise
You need each note together with the total number of notes its author has written, without losing any rows. What fits best?
Show answer
Answer: A. A window function computes the per-user count but keeps every note row. GROUP BY would collapse the notes into one row per user.
Key takeaways
- Aggregates turn many rows into one;
FILTERcomputes several conditional aggregates in one pass. GROUP BYmakes groups,HAVINGfilters them; every selected column must be grouped or aggregated.WITHnames intermediate steps;WITH RECURSIVEwalks hierarchies.- Window functions (
OVER (...)) compute across related rows without collapsing them: rankings, previous values, running totals and shares.
FAQ
Is count(*) slow on big tables?
It reads the whole table (or a whole index) because Postgres keeps no stored row count, since different transactions may see different numbers of rows. For an estimate, read reltuples from pg_class; for exact counts on huge tables, keep a counter table.
What is the difference between count(*) and count(1)?
None in practice. Both count rows. count(col) is different: it skips NULLs.
Are CTEs slower than subqueries?
Not anymore. Since PostgreSQL 12, a CTE that is referenced once and has no side effects is inlined into the main query and optimised the same way as a subquery.
Can I use a window function in WHERE?
No, window functions are computed after WHERE and GROUP BY. Compute it in a subquery or CTE and filter in the outer query, for example SELECT * FROM (SELECT ..., row_number() OVER (...) AS nth FROM notes) s WHERE nth = 1.
How do I get the median?
Use the ordered-set aggregate percentile_cont(0.5) WITHIN GROUP (ORDER BY col). percentile_disc returns an actual value from the data instead of an interpolated one.