Aggregation & Window Functions

Aggregation collapses many rows into one summary row. Window functions compute across a set of rows without collapsing them. Learning where that line falls is the whole topic.

Examples continue the books / customers / orders schema from joins.

Aggregate functions

An aggregate takes a column of values from many rows and returns one value.

Function	Returns
COUNT(*)	Number of rows
COUNT(expr)	Number of rows where expr is not null
COUNT(DISTINCT expr)	Number of distinct non-null values
SUM(expr)	Sum of non-null values, NULL if there are none
AVG(expr)	Mean of non-null values
MIN(expr) / MAX(expr)	Smallest / largest non-null value

SELECT
  COUNT(*)          AS rows,
  COUNT(year)       AS with_year,
  COUNT(DISTINCT author_id) AS distinct_authors,
  SUM(price)        AS total,
  AVG(price)        AS mean,
  MIN(year)         AS earliest,
  MAX(price)        AS priciest
FROM books;

 rows | with_year | distinct_authors | total | mean  | earliest | priciest
------+-----------+------------------+-------+-------+----------+----------
    4 |         4 |                3 | 55.74 | 13.93 |     1968 |    16.00

Aggregates ignore NULLs

Every aggregate except COUNT(*) skips null inputs. This is not a rounding detail — it changes answers.

-- prices: 10, 20, NULL
SELECT COUNT(*), COUNT(price), SUM(price), AVG(price) FROM t;
-- => 3 | 2 | 30 | 15      (not 10, because AVG divides by 2, not 3)

If you want nulls treated as zero, say so:

SELECT AVG(COALESCE(price, 0)) FROM t;   -- => 10

COUNT(*) vs COUNT(col)

COUNT(*) counts rows; it never looks at values and never returns null. COUNT(col) counts non-null values in that column.

This matters most after an outer join, where the unmatched side is all nulls:

SELECT c.name, COUNT(*) AS wrong, COUNT(o.id) AS right
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

 name  | wrong | right
-------+-------+-------
 Amina |     2 |     2
 Bruno |     1 |     1
 Chidi |     1 |     0     <- one all-NULL row exists, but zero real orders

Tip

Rule of thumb: use COUNT(*) when counting rows of the left table, and COUNT(pk_of_right_table) when counting matches from an outer-joined table.

Empty input

An aggregate over zero rows returns NULL — except COUNT, which returns 0.

SELECT SUM(price) FROM books WHERE year > 3000;   -- NULL
SELECT COUNT(*)   FROM books WHERE year > 3000;   -- 0

Wrap in COALESCE when a number is required:

SELECT COALESCE(SUM(price), 0) FROM books WHERE year > 3000;   -- 0

String aggregation

Both engines can concatenate a group into one string, with different names:

-- SQLite
SELECT author_id, GROUP_CONCAT(title, ', ') AS titles FROM books GROUP BY author_id;

-- PostgreSQL
SELECT author_id, STRING_AGG(title, ', ' ORDER BY title) AS titles FROM books GROUP BY author_id;

PostgreSQL lets you order inside the aggregate (ORDER BY before the closing paren) and also has ARRAY_AGG, JSON_AGG, and JSONB_AGG. SQLite 3.44+ accepts ORDER BY in aggregate calls too, and has JSON_GROUP_ARRAY / JSON_GROUP_OBJECT; older SQLite builds return group-concatenated values in an unspecified order.

Types returned

Expression	SQLite	PostgreSQL
SUM(integer_col)	integer (or float on overflow)	bigint
SUM(numeric_col)	float	numeric (exact)
AVG(integer_col)	float	numeric
COUNT(...)	integer	bigint

SQLite also has TOTAL(x), identical to SUM except it returns 0.0 instead of NULL for empty input and always returns a float.

GROUP BY

GROUP BY splits rows into groups; each aggregate then runs once per group, producing one output row per group.

SELECT author_id, COUNT(*) AS books, ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY author_id
ORDER BY books DESC;

 author_id | books | avg_price
-----------+-------+-----------
         1 |     2 |     13.25
         2 |     1 |     16.00
         3 |     1 |     13.25

Group by multiple columns to get one row per distinct combination:

SELECT author_id, year, COUNT(*) FROM books GROUP BY author_id, year;

GROUP BY treats all NULLs as one group — the only place in SQL where nulls are considered equal for grouping.

The bare-column rule

In standard SQL, every expression in SELECT must be either inside an aggregate or listed in GROUP BY. Anything else is ambiguous: which of the many values in the group did you mean?

-- Ambiguous: which title?
SELECT author_id, title, COUNT(*) FROM books GROUP BY author_id;

Caution

PostgreSQL rejects this: column "books.title" must appear in the GROUP BY clause or be used in an aggregate function.

SQLite accepts it and returns an arbitrarily chosen title from each group. That is a portability landmine: SQL that works locally on SQLite fails in production on PostgreSQL.

PostgreSQL relaxes the rule when the grouping column is a primary key, because every other column of that table is then functionally dependent on it:

-- Legal in PostgreSQL: a.name is determined by a.id, which is the PK
SELECT a.id, a.name, COUNT(b.id)
FROM authors a LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.id;

Both engines allow grouping by an output alias or a column position, which is a non-standard convenience:

SELECT year / 10 * 10 AS decade, COUNT(*) FROM books GROUP BY decade;
SELECT author_id, COUNT(*) FROM books GROUP BY 1;

Note

SQLite has one useful, documented exception to the arbitrary-value rule: when the query’s only aggregate is a single MIN() or MAX(), bare columns come from the row that produced that minimum or maximum.

-- SQLite only: the title of the priciest book per author
SELECT author_id, MAX(price), title FROM books GROUP BY author_id;

PostgreSQL has no such rule; use DISTINCT ON or a window function instead.

HAVING vs WHERE

Both filter, at different stages.

• WHERE filters individual rows before grouping. It cannot see aggregates.
• HAVING filters groups after aggregation. It can see aggregates.

SELECT author_id, COUNT(*) AS books, SUM(price) AS revenue
FROM books
WHERE year >= 1970                 -- drop old books before counting
GROUP BY author_id
HAVING COUNT(*) >= 1 AND SUM(price) > 13   -- drop small groups after counting
ORDER BY revenue DESC;

Writing WHERE COUNT(*) > 1 is an error in both engines — the count doesn’t exist yet at WHERE time.

Tip

Put a condition in WHERE whenever you can. WHERE reduces the rows the engine must group; HAVING throws work away after it’s been done. Only genuinely aggregate conditions belong in HAVING.

HAVING without GROUP BY is legal — the whole result is one group:

SELECT SUM(price) FROM books HAVING SUM(price) > 1000;   -- 0 or 1 rows

FILTER: conditional aggregation

FILTER (WHERE ...) applies a condition to a single aggregate. It is standard SQL, supported in PostgreSQL 9.4+ and SQLite 3.30+.

SELECT
  author_id,
  COUNT(*)                                  AS total,
  COUNT(*) FILTER (WHERE year < 1980)       AS pre_1980,
  SUM(price) FILTER (WHERE year >= 1980)    AS revenue_since_1980
FROM books
GROUP BY author_id;

The older portable form uses CASE, and works on any version:

SELECT
  author_id,
  COUNT(CASE WHEN year < 1980 THEN 1 END) AS pre_1980,
  SUM(CASE WHEN year >= 1980 THEN price ELSE 0 END) AS revenue_since_1980
FROM books
GROUP BY author_id;

(COUNT works because the missing ELSE yields NULL, which COUNT skips.)

GROUPING SETS, ROLLUP, CUBE (PostgreSQL only)

Multiple grouping levels in one pass, with subtotals:

-- PostgreSQL
SELECT author_id, year, COUNT(*)
FROM books
GROUP BY ROLLUP (author_id, year);

This returns per-(author, year) rows, per-author subtotals, and one grand total, with NULL marking the rolled-up columns. GROUPING(col) tells you whether a NULL is a real value or a subtotal marker. SQLite has none of this — use UNION ALL of separate grouped queries.

Grouping gotchas

Joins multiply rows before aggregation. If each customer has 3 orders and 2 addresses, joining both and summing order totals triples the answer. Aggregate each side separately first:

SELECT c.name, o.order_total
FROM customers c
LEFT JOIN (
  SELECT customer_id, SUM(qty) AS order_total FROM orders GROUP BY customer_id
) AS o ON o.customer_id = c.id;

COUNT(DISTINCT x) is not free. It requires a sort or hash per group. On large tables in PostgreSQL, consider approximations or pre-aggregation.

Groups with zero rows don’t appear. GROUP BY can only produce groups that exist in the data. To show a customer with zero orders, LEFT JOIN from the customers table (as above) — grouping the orders table alone will never invent a row for them.

Window functions

A window function computes a value across a set of related rows while keeping every input row in the output. Supported in PostgreSQL 8.4+ and SQLite 3.25+ (2018).

SELECT
  title,
  price,
  AVG(price) OVER () AS overall_avg,
  price - AVG(price) OVER () AS diff
FROM books;

        title         | price | overall_avg | diff
----------------------+-------+-------------+-------
 The Dispossessed     | 14.99 |      13.935 |  1.055
 A Wizard of Earthsea | 11.50 |      13.935 | -2.435
 Stories of Your Life | 16.00 |      13.935 |  2.065
 Kindred              | 13.25 |      13.935 | -0.685

All four rows survive. GROUP BY would have returned one.

The OVER clause

function(...) OVER (
  PARTITION BY expr, ...     -- split rows into independent groups
  ORDER BY expr [ASC|DESC]   -- order within each partition
  frame_clause               -- which rows within the partition count
)

PARTITION BY is “GROUP BY for the window” — the function restarts for each partition.

SELECT
  author_id,
  title,
  price,
  AVG(price) OVER (PARTITION BY author_id) AS author_avg,
  COUNT(*)   OVER (PARTITION BY author_id) AS books_by_author
FROM books
ORDER BY author_id;

Ranking functions

These require ORDER BY inside OVER.

Function	Behaviour on ties
ROW_NUMBER()	Always 1, 2, 3, 4 — ties broken arbitrarily
RANK()	1, 2, 2, 4 — gaps after ties
DENSE_RANK()	1, 2, 2, 3 — no gaps
NTILE(n)	Splits rows into n roughly equal buckets
PERCENT_RANK(), CUME_DIST()	Relative position, 0–1

SELECT
  title, price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
  RANK()       OVER (ORDER BY price DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense
FROM books;

Top-N per group

The most valuable window pattern. Rank within each partition, then filter.

SELECT author_id, title, price
FROM (
  SELECT author_id, title, price,
         ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY price DESC) AS rn
  FROM books
) AS ranked
WHERE rn = 1;

Caution

You cannot filter on a window function in WHERE or HAVING. Window functions are evaluated after WHERE, GROUP BY, and HAVING, and only just before ORDER BY. Wrap the query in a subquery or CTE, as above. Neither engine has the QUALIFY clause some other databases offer.

LAG and LEAD

Reach into the previous or next row of the partition — the tool for deltas and gap detection.

SELECT
  year,
  title,
  LAG(year)  OVER (ORDER BY year) AS prev_year,
  LEAD(year) OVER (ORDER BY year) AS next_year,
  year - LAG(year) OVER (ORDER BY year) AS gap
FROM books;

Both take optional offset and default arguments: LAG(year, 1, 0) looks back one row and returns 0 at the boundary instead of NULL. Available in both engines.

Running totals and frames

A frame defines which rows within the partition the function sees. Adding ORDER BY to OVER changes the default frame from “the whole partition” to “everything up to and including the current row”.

SELECT
  year, title, price,
  SUM(price) OVER (ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_total
FROM books
ORDER BY year;

 year |        title         | price | running_total
------+----------------------+-------+---------------
 1968 | A Wizard of Earthsea | 11.50 |         11.50
 1974 | The Dispossessed     | 14.99 |         26.49
 1979 | Kindred              | 13.25 |         39.74
 2002 | Stories of Your Life | 16.00 |         55.74

Frame syntax:

ROWS   BETWEEN <start> AND <end>    -- counts physical rows
RANGE  BETWEEN <start> AND <end>    -- counts rows with equal ORDER BY values as one
GROUPS BETWEEN <start> AND <end>    -- counts peer groups (PostgreSQL 11+, SQLite 3.28+)

<start>/<end> ::= UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW
                | n FOLLOWING | UNBOUNDED FOLLOWING

A 3-row moving average:

SELECT year, price,
       AVG(price) OVER (ORDER BY year ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM books;

Caution

The default frame when you write ORDER BY inside OVER is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes all peers — rows with the same ORDER BY value. If two books share a year, a running total using the default frame gives both the same value (the total including both). Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want strict row-by-row accumulation. Both engines behave identically here.

LAST_VALUE has the same trap, and it bites more often:

-- Returns the current row's value, not the partition's last, because the
-- default frame ends at CURRENT ROW
LAST_VALUE(price) OVER (ORDER BY year)

-- Correct
LAST_VALUE(price) OVER (ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FIRST_VALUE works as expected with the default frame; NTH_VALUE(expr, n) needs the same care as LAST_VALUE.

Named windows

Repeating a long OVER (...) is noise. Name it once with a WINDOW clause — supported in PostgreSQL and SQLite 3.25+.

SELECT
  author_id, title, price,
  ROW_NUMBER() OVER w AS rn,
  SUM(price)   OVER w AS running,
  RANK()       OVER w AS rnk
FROM books
WINDOW w AS (PARTITION BY author_id ORDER BY price DESC)
ORDER BY author_id, rn;

The WINDOW clause sits between HAVING and ORDER BY.

Aggregates as window functions

Any aggregate can be used with OVER, including FILTER:

SELECT
  author_id, title, price,
  SUM(price)   OVER (PARTITION BY author_id) AS author_total,
  price * 100.0 / SUM(price) OVER (PARTITION BY author_id) AS pct_of_author,
  COUNT(*) FILTER (WHERE year < 1980) OVER (PARTITION BY author_id) AS old_books
FROM books;

The “percent of group total” pattern above is one line with a window and requires a self-join or subquery without one.

You can also combine GROUP BY and windows in the same query — the window runs on the grouped rows:

SELECT
  author_id,
  SUM(price) AS total,
  SUM(SUM(price)) OVER () AS grand_total,
  SUM(price) * 100.0 / SUM(SUM(price)) OVER () AS pct
FROM books
GROUP BY author_id;

The nested SUM(SUM(price)) looks strange but is correct: the inner SUM aggregates within the group, the outer one is a window over the grouped result.

Key points

• Aggregates ignore NULL; COUNT(*) counts rows, COUNT(col) counts non-null values — the difference decides most outer-join bugs.
• Aggregates over zero rows return NULL, except COUNT which returns 0.
• WHERE filters rows before grouping, HAVING filters groups after — prefer WHERE.
• PostgreSQL enforces the bare-column rule; SQLite silently returns an arbitrary value. Write standard SQL.
• FILTER (WHERE ...) works in PostgreSQL 9.4+ and SQLite 3.30+; CASE inside an aggregate is the universal fallback.
• Window functions keep every row; they run after HAVING, so filter them in a subquery or CTE.
• ROW_NUMBER/RANK/DENSE_RANK differ only on ties; top-N-per-group is ROW_NUMBER + outer filter.
• The default frame is RANGE ... CURRENT ROW, which includes peers — use ROWS for true running totals, and give LAST_VALUE an explicit full frame.