Aggregation, Grouping and Window Functions

You need each customer with their total spend, so you write GROUP BY customer_id and the totals are right. Then you add the email and MySQL refuses, because the column is neither grouped nor aggregated. You add it to the GROUP BY, the query runs, and the totals are wrong, because the added column split groups you meant to keep together. Two failures from one clause, both of them the clause doing its job.

Grouping makes many rows into one, and everything after that point works with the collapsed shape. Aggregates count rows, sum columns and concatenate values. Window functions apply the same aggregates without collapsing anything, so the row you started with is still there when the total arrives.

The dataset is the customer and orders pair from lesson one. Order 1004 is refunded, order 1006 is pending, and customers 3 and 5 have no orders at all, which is the case that reveals what COUNT is counting.

COUNT(*) counts rows and COUNT(column) counts values

Every aggregate except COUNT(*) ignores NULL, because the aggregate is applied to the values in the column and NULL is not a value. So COUNT(*) counts rows, COUNT(column) counts rows where that column is not NULL, and the gap between them is the number of NULLs.

CREATE TABLE call_log (
  call_id     INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NULL,
  note        VARCHAR(64) NOT NULL
) ENGINE=InnoDB;

INSERT INTO call_log (call_id, customer_id, note) VALUES
 (1, 1,    'asked for invoice'),
 (2, NULL, 'wrong number'),
 (3, 3,    'asked for invoice'),
 (4, 99,   'orphan import');

SELECT COUNT(*) AS rows_n,
       COUNT(customer_id) AS with_customer,
       COUNT(*) - COUNT(customer_id) AS missing
FROM call_log;

+--------+---------------+---------+
| rows_n | with_customer | missing |
+--------+---------------+---------+
|      4 |             3 |       1 |
+--------+---------------+---------+

One call has no customer, so the two counts differ by one. COUNT(*) - COUNT(column) is the shortest way to find the NULL count in a column, and when the answer should be zero it is a data-quality check you can run in a report.

The same rule produces the most common NULL bug in reporting: after a left join the padded rows have NULL in every right-hand column, so COUNT(o.order_id) is a count of orders and COUNT(*) is a count of rows.

SELECT COUNT(*) AS rows_returned,
       COUNT(o.order_id) AS orders,
       COUNT(DISTINCT c.customer_id) AS customers
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

+---------------+--------+-----------+
| rows_returned | orders | customers |
+---------------+--------+-----------+
|             7 |      7 |         3 |
+---------------+--------+-----------+

Seven rows, seven orders, three customers with orders. Customers 3 and 5 produce no row, because the left join pads the order columns and generates nothing for a customer with no orders. A count that includes them must come from the customer side before the join, or from its own aggregate.

Every non-aggregated column must be in the GROUP BY

When you group, each output row represents several input rows. A column that is not aggregated has no single value to show, so MySQL with ONLY_FULL_GROUP_BY enabled, which is the default in MySQL 8 and later, rejects the query outright:

SELECT customer_id, status, SUM(total_cents) AS cents
FROM orders
GROUP BY customer_id;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'orders.status' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

SQLite does not reject it. It picks a value from one of the rows in the group, which is worse than an error because the result looks fine:

SELECT customer_id, status, SUM(total_cents) AS cents
FROM orders
GROUP BY customer_id;

┌─────────────┬──────────┬───────┐
│ customer_id │  status  │ cents │
├─────────────┼──────────┼───────┤
│ 1           │ paid     │ 21000 │
│ 2           │ pending  │ 32400 │
│ 4           │ refunded │  4800 │
└─────────────┴──────────┴───────┘

Customer 2 has a pending order and a paid order, and the engine reported pending. Which status it reports is an implementation detail that can change with the plan, and the number beside it is a sum across both orders, so the row reads as a sentence about nothing. MySQL's error is the behaviour you want. If you want an arbitrary value, write ANY_VALUE(status), which documents the intent.

The repair depends on the question: include the column in the GROUP BY if it participates in the grouping key, or leave it out and pick it up in a later join. Do not add columns one at a time until the error goes away, because each addition can split groups and change the totals.

HAVING filters groups and WHERE filters rows

WHERE runs before grouping, so it sees individual rows and cannot see aggregates. HAVING runs after grouping, so it sees groups and can. That pair of facts is the whole distinction, and the practical rule is that a condition on a raw column belongs in WHERE and a condition on an aggregate belongs in HAVING.

SELECT c.customer_id, c.email, COUNT(*) AS paid_orders,
       SUM(o.total_cents) / 100.0 AS paid_euros
FROM customer c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status <> 'pending'
GROUP BY c.customer_id, c.email
HAVING COUNT(*) >= 2 AND SUM(o.total_cents) > 10000
ORDER BY paid_euros DESC;

+-------------+-----------------+-------------+------------+
| customer_id | email           | paid_orders | paid_euros |
+-------------+-----------------+-------------+------------+
|           1 | ana@example.com |           3 |   210.0000 |
+-------------+-----------------+-------------+------------+

MySQL and SQLite allow the SELECT alias inside HAVING, because HAVING is evaluated after the projection. PostgreSQL does not, so HAVING paid_euros > 100 would need the expression repeated there.

Two more notes. HAVING works without a GROUP BY, in which case the whole result is one group: legal, rarely intended. And moving a row-level condition into HAVING is a performance regression as well, because the engine groups rows it is about to throw away.

GROUP_CONCAT, string_agg and array_agg build one value from many

Sometimes the collapsed answer is a list. Ordering matters more than people expect, because a concatenated list without an ORDER BY is as unreproducible as a LIMIT without one.

SELECT c.customer_id, c.email, COUNT(o.order_id) AS orders,
       GROUP_CONCAT(o.order_id ORDER BY o.order_id SEPARATOR ', ') AS order_ids,
       SUM(o.total_cents) AS cents
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.email
ORDER BY c.customer_id;

+-------------+-----------------+--------+------------------+-------+
| customer_id | email           | orders | order_ids        | cents |
+-------------+-----------------+--------+------------------+-------+
|           1 | ana@example.com |      3 | 1001, 1002, 1007 | 21000 |
|           2 | bo@example.com  |      2 | 1003, 1005       | 32400 |
|           3 | cai@example.com |      0 | NULL             |  NULL |
|           4 | dee@example.com |      2 | 1004, 1006       |  4800 |
|           5 | eve@example.com |      0 | NULL             |  NULL |
+-------------+-----------------+--------+------------------+-------+

Two rows carry NULL for the list and for the sum, for the reason from the COUNT section: an aggregate over no values is NULL, not zero and not an empty string. The ORDER BY inside GROUP_CONCAT is part of the MySQL syntax, and without it the order of the ids is whatever the engine produced.

The function name changes by engine. PostgreSQL and SQLite 3.51 have string_agg(value, delimiter), and PostgreSQL has array_agg when the result should be an array. MySQL 9.6 has neither; the equivalent is GROUP_CONCAT, and SEPARATOR changes the delimiter from the default comma.

A window function aggregates without collapsing the rows

This is the payoff of the module. An aggregate with an OVER clause becomes a window function: it computes over a set of rows related to the current row and writes the result back onto that row. The partition defines the set, the order defines the sequence within it, and the output row count equals the input row count.

SELECT order_id, customer_id, total_cents,
       SUM(total_cents) OVER (PARTITION BY customer_id) AS customer_total,
       ROUND(100.0 * total_cents
             / SUM(total_cents) OVER (PARTITION BY customer_id), 1) AS pct
FROM orders
ORDER BY customer_id, order_id;

+----------+-------------+-------------+----------------+------+
| order_id | customer_id | total_cents | customer_total | pct  |
+----------+-------------+-------------+----------------+------+
|     1001 |           1 |       12000 |          21000 | 57.1 |
|     1002 |           1 |        4500 |          21000 | 21.4 |
|     1007 |           1 |        4500 |          21000 | 21.4 |
|     1003 |           2 |        9900 |          32400 | 30.6 |
|     1005 |           2 |       22500 |          32400 | 69.4 |
|     1004 |           4 |        3000 |          4800 | 62.5 |
|     1006 |           4 |        1800 |        4800 | 37.5 |
+----------+-------------+-------------+----------------+------+

Seven rows in, seven rows out. Every order keeps its own total and gains the customer total beside it, which no GROUP BY can do: grouping would have merged the three customer-1 orders and lost the individual amounts.

ROW_NUMBER, RANK and DENSE_RANK disagree about ties

The three ranking functions differ only in how they treat equal sort keys, and the difference is easiest to see on a small table with a deliberate tie:

WITH t(label, cents) AS (
  SELECT 'a', 12000 UNION ALL
  SELECT 'b', 4500  UNION ALL
  SELECT 'c', 4500  UNION ALL
  SELECT 'd', 1800
)
SELECT label, cents,
       ROW_NUMBER() OVER (ORDER BY cents DESC) AS rn,
       RANK()       OVER (ORDER BY cents DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY cents DESC) AS dense
FROM t
ORDER BY cents DESC, label;

+-------+-------+----+-----+-------+
| label | cents | rn | rnk | dense |
+-------+-------+----+-----+-------+
| a     | 12000 |  1 |   1 |     1 |
| b     |  4500 |  2 |   2 |     2 |
| c     |  4500 |  3 |   2 |     2 |
| d     |  1800 |  4 |   4 |     3 |
+-------+-------+----+-----+-------+

ROW_NUMBER assigns distinct numbers and breaks the tie arbitrarily, so b and c get 2 and 3 in whatever order the engine produced them. RANK gives both tied rows the same number and skips, so the next is 4: two runners tied for second, the next one fourth. DENSE_RANK does not skip, so the next is 3.

For "the most recent order per customer" that choice has a consequence. WHERE rn = 1 returns one row per customer by construction, while WHERE rnk = 1 can return two rows when two orders share the top value. For ROW_NUMBER to be reproducible, the window needs a tie-breaker for the same reason an ORDER BY does.

A window function replaces the self join you were going to write

The classic query people solve with a subquery or self join is "latest row per group". That shape has a failure mode the window function does not. Start with the correlated subquery:

SELECT o.customer_id, o.order_id, o.placed_at
FROM orders o
WHERE o.placed_at = (
  SELECT MAX(o2.placed_at) FROM orders o2 WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id, o.order_id;

+-------------+----------+---------------------+
| customer_id | order_id | placed_at           |
+-------------+----------+---------------------+
|           1 |     1007 | 2026-04-15 13:05:00 |
|           2 |     1005 | 2026-04-04 19:55:00 |
|           4 |     1006 | 2026-04-09 10:10:00 |
+-------------+----------+---------------------+

Three rows, and the right three. Now add an order for customer 2 with the same placed_at as 1005, and the query returns four rows because the predicate is equality on a non-unique column:

INSERT INTO orders VALUES (1008, 2, 'paid', '2026-04-04 19:55:00', 500);

-- the same statement now returns two rows for customer 2
SELECT o.customer_id, o.order_id FROM orders o
WHERE o.placed_at = (
  SELECT MAX(o2.placed_at) FROM orders o2 WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id, o.order_id;

+-------------+----------+
| customer_id | order_id |
+-------------+----------+
|           1 |     1007 |
|           2 |     1005 |
|           2 |     1008 |
|           4 |     1006 |
+-------------+----------+

The window version does not have that failure mode, because the ranking is computed inside the window and the filter happens after the numbers exist:

SELECT customer_id, order_id, placed_at, total_cents
FROM (
  SELECT customer_id, order_id, placed_at, total_cents,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY placed_at DESC, order_id DESC
         ) AS rn
  FROM orders
) ranked
WHERE rn = 1
ORDER BY customer_id;

+-------------+----------+---------------------+-------------+
| customer_id | order_id | placed_at           | total_cents |
+-------------+----------+---------------------+-------------+
|           1 |     1007 | 2026-04-15 13:05:00 |        4500 |
|           2 |     1008 | 2026-04-04 19:55:00 |         500 |
|           4 |     1006 | 2026-04-09 10:10:00 |        1800 |
+-------------+----------+---------------------+-------------+

Still three rows, one per customer, with the tie broken on order_id. The window function does not make the tie disappear; it makes the tie-breaking your decision instead of the plan's.

A running total is the same mechanism with a frame. ORDER BY inside the window turns the default frame from the whole partition into everything up to the current row, so the sum accumulates:

SELECT order_id, placed_at, total_cents,
       SUM(total_cents) OVER (
         ORDER BY placed_at, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
WHERE customer_id = 1
ORDER BY placed_at, order_id;

+----------+---------------------+-------------+---------------+
| order_id | placed_at           | total_cents | running_total |
+----------+---------------------+-------------+---------------+
|     1001 | 2026-03-02 09:14:00 |       12000 |         12000 |
|     1002 | 2026-03-11 16:40:00 |        4500 |         16500 |
|     1007 | 2026-04-15 13:05:00 |        4500 |         21000 |
+----------+---------------------+-------------+---------------+

The explicit frame is worth writing even though it is the default for an ordered window, because a reader can see what you meant and because the default frame differs between ROWS and RANGE mode. Window functions arrived in MySQL 8.0, SQLite 3.25 and PostgreSQL 8.4, so the self join version is a choice rather than a requirement.

SELECT c.customer_id, c.email,
       COUNT(o.order_id) AS orders,
       COALESCE(SUM(o.total_cents), 0) / 100.0 AS euros,
       (SELECT COUNT(*) - COUNT(customer_id) FROM call_log) AS null_calls
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.email
ORDER BY c.customer_id;

SELECT c.customer_id, c.email, SUM(o.total_cents) / 100.0 AS paid_euros
FROM customer c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id, c.email
HAVING SUM(o.total_cents) > 10000
ORDER BY paid_euros DESC;

+-------------+-----------------+------------+
| customer_id | email           | paid_euros |
+-------------+-----------------+------------+
|           2 | bo@example.com  |   225.0000 |
|           1 | ana@example.com |   210.0000 |
+-------------+-----------------+------------+

SELECT c.customer_id,
       GROUP_CONCAT(o.order_id ORDER BY o.order_id SEPARATOR ',') AS by_id,
       GROUP_CONCAT(o.order_id ORDER BY o.total_cents DESC, o.order_id
                    SEPARATOR ',') AS by_amount
FROM customer c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

+-------------+----------------+-----------+
| customer_id | by_id          | by_amount |
+-------------+----------------+-----------+
|           1 | 1001,1002,1007 | 1001,1002,1007 |
|           2 | 1003,1005      | 1005,1003 |
|           4 | 1004,1006      | 1004,1006 |
+-------------+----------------+-----------+

SELECT order_id, customer_id, placed_at, total_cents,
       SUM(total_cents) OVER w AS running_total,
       ROUND(100.0 * total_cents
             / SUM(total_cents) OVER (PARTITION BY customer_id), 1) AS pct_of_customer,
       RANK() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS amount_rank
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at, order_id)
ORDER BY customer_id, placed_at, order_id;

+----------+-------------+---------------------+-------------+---------------+-----------------+-------------+
| order_id | customer_id | placed_at           | total_cents | running_total | pct_of_customer | amount_rank |
+----------+-------------+---------------------+-------------+---------------+-----------------+-------------+
|     1001 |           1 | 2026-03-02 09:14:00 |       12000 |         12000 |            57.1 |           1 |
|     1002 |           1 | 2026-03-11 16:40:00 |        4500 |         16500 |            21.4 |           2 |
|     1007 |           1 | 2026-04-15 13:05:00 |        4500 |         21000 |            21.4 |           2 |
|     1003 |           2 | 2026-03-12 11:02:00 |        9900 |          9900 |            30.6 |           2 |
|     1005 |           2 | 2026-04-04 19:55:00 |       22500 |         32400 |            69.4 |           1 |
|     1004 |           4 | 2026-04-01 08:20:00 |        3000 |          3000 |            62.5 |           1 |
|     1006 |           4 | 2026-04-09 10:10:00 |        1800 |          4800 |            37.5 |           2 |
+----------+-------------+---------------------+-------------+---------------+-----------------+-------------+