Up to now every query has returned the same number of rows it scanned (or fewer, after WHERE / LIMIT). Aggregates do something different: they reduce a whole group of rows down to one. Without GROUP BY (next section) that "group" is the entire result set, so every aggregate query so far in this chapter returns exactly one row.

-- The whole table is one big group
SELECT COUNT(*) AS total_orders FROM orders;

total_orders
------------
4

The five core aggregates

-- COUNT — how many rows?
SELECT COUNT(*) FROM orders;                       -- 4

-- SUM — total of a numeric column
SELECT SUM(quantity * unit_price) FROM order_items; -- 173.47

-- AVG — arithmetic mean of a numeric column
SELECT AVG(price) FROM products;                   -- 28.83

-- MIN / MAX — smallest / largest value (works on numbers, dates, strings)
SELECT MIN(price), MAX(price) FROM products;       -- 9.50, 42.00
SELECT MIN(placed_at), MAX(placed_at) FROM orders; -- earliest / latest date

COUNT(*) vs COUNT(col) — the NULL difference

This is the most-asked aggregate question. The two forms count different things:

• COUNT(*) counts rows — whether any column is NULL or not.
• COUNT(col) counts rows where that column is not NULL.
• COUNT(DISTINCT col) counts the number of distinct non-NULL values.

All aggregates skip NULL (not just COUNT)

The rule generalises: every aggregate ignores NULLs in its input. SUM, AVG, MIN, MAX all drop NULL rows before doing the math. This matters most for AVG — averaging "missing" as zero is almost never what you want, and SQL deliberately makes the safer choice.

-- Suppose products had a NULL price for "TBD" items:
--   id | name        | price
--    1 | Mug         |  9.50
--    2 | Sticker     |  3.00
--    3 | Future Book |  NULL
SELECT COUNT(*), COUNT(price), SUM(price), AVG(price), MIN(price), MAX(price)
FROM   products;
-- COUNT(*) = 3   COUNT(price) = 2
-- SUM      = 12.50            -- NULL contributed 0
-- AVG      = 6.25             -- 12.50 / 2, not / 3 — NULL row excluded
-- MIN/MAX  = 3.00 / 9.50      -- NULL ignored

COALESCE — substitute a value for NULL

This is where COALESCE finally earns a section, exactly where it's most useful: cleaning up aggregates. COALESCE(a, b, c, …) returns the first non-NULL argument. Two common patterns:

-- ① Treat a missing price as 0 BEFORE aggregating
--    AVG now divides by 3, not 2 — careful, this changes the answer
SELECT AVG(COALESCE(price, 0)) FROM products;   -- 4.17, not 6.25

-- ② Replace a NULL aggregate result with a friendlier value
--    SUM on an empty table returns NULL; we want 0:
SELECT COALESCE(SUM(price), 0) AS total_revenue
FROM   products
WHERE  category = 'never-existed';   -- empty result → SUM = NULL → COALESCE = 0

Aggregates combined with WHERE

WHERE runs before the aggregate (remember the execution-order diagram from Chapter 01). So a filter naturally shrinks the input the aggregate sees.

-- Average price of books only
SELECT AVG(price) FROM products WHERE category = 'books';

-- Number of paid orders
SELECT COUNT(*) FROM orders WHERE status = 'paid';

-- Total revenue from non-cancelled orders
SELECT SUM(quantity * unit_price) AS revenue_usd
FROM   order_items oi
JOIN   orders o ON o.id = oi.order_id   -- Chapter 05
WHERE  o.status != 'cancelled';

An aggregate without GROUP BY reduces the whole result set to one row. GROUP BY says: partition the rows into buckets first, then aggregate inside each bucket separately. One output row per bucket.

SELECT category,
       COUNT(*)   AS product_count,
       AVG(price) AS avg_price,
       MIN(price) AS cheapest,
       MAX(price) AS priciest
FROM   products
GROUP BY category;

category | product_count | avg_price | cheapest | priciest
---------+---------------+-----------+----------+---------
books    |             2 |     38.50 |    34.99 |    42.00
merch    |             1 |      9.50 |     9.50 |     9.50

Multi-column GROUP BY

List more than one column and the buckets become "every distinct combination" of those columns. The result has one row per (col₁, col₂, …) tuple.

-- One row per (status, day)
SELECT status,
       DATE(placed_at) AS day,
       COUNT(*)        AS orders,
       SUM(quantity * unit_price) AS revenue
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.id
GROUP BY status, DATE(placed_at)
ORDER BY day, status;

The "what can I put in SELECT?" rule

Once a query has GROUP BY, every column in the SELECT list must be one of:

1. A column listed in the GROUP BY clause, or
2. Wrapped in an aggregate function (SUM, COUNT, etc.), or
3. A constant or a function of (1) and (2).

The reason is simple: the result has one row per bucket, so any column that isn't a grouping key or an aggregate has multiple possible values per row and no rule for which to pick.

WHERE filters individual rows before they enter a bucket. HAVING filters whole buckets after the aggregate has run. They look similar, but they run at different points in the pipeline, and they answer different questions.

Side-by-side example

"Show me categories whose average price is above $20, considering only products priced over $5." Two filters, two clauses — and they're not interchangeable.

SELECT category,
       AVG(price) AS avg_price,
       COUNT(*)   AS items
FROM   products
WHERE  price > 5            -- ① row-level: drop the $3 sticker before bucketing
GROUP BY category
HAVING AVG(price) > 20      -- ② bucket-level: only keep categories whose avg > 20
ORDER BY avg_price DESC;

category | avg_price | items
---------+-----------+------
books    |     38.50 |     2

Two failures the diagram explains

-- ⚠ ① Aggregate in WHERE — illegal
SELECT category, AVG(price)
FROM   products
WHERE  AVG(price) > 20         -- ERROR: aggregate functions are not allowed in WHERE
GROUP BY category;
-- why: WHERE runs at step 2; buckets don't exist until step 3, so AVG has nothing to operate on yet.

-- ⚠ ② Bare-column filter in HAVING — works but pointless
SELECT category, AVG(price)
FROM   products
GROUP BY category
HAVING category = 'books';     -- "works" but throws away whole buckets after building them
-- Equivalent and faster:
SELECT category, AVG(price)
FROM   products
WHERE  category = 'books'      -- skip non-book rows before any aggregation happens
GROUP BY category;

GROUP BY collapses each bucket into one row. That's powerful, but it means you lose the individual rows. Window functions let you compute the same per-bucket numbers and keep every row. Every row carries the aggregate alongside its own values — handy for running totals, rankings, and "how do I compare to my group average?"

The OVER (…) clause

Any aggregate becomes a window function when followed by OVER (…). The parenthesised expression is the window definition: which rows count as "this row's group" and (sometimes) what order they're in. Two clauses inside OVER matter at this introductory level:

• PARTITION BY col — split rows into independent windows (analogous to GROUP BY's buckets).
• ORDER BY col — order the rows within each window. Required for ranking and running-total functions.

-- Per-row category average (the bottom panel of the diagram above)
SELECT category,
       price,
       AVG(price)   OVER (PARTITION BY category) AS avg_in_cat,
       price - AVG(price) OVER (PARTITION BY category) AS delta_from_cat_avg
FROM   products;

-- Running total of order revenue, in date order
SELECT id,
       placed_at,
       (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id) AS order_total,
       SUM(
         (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = o.id)
       ) OVER (ORDER BY placed_at) AS running_total
FROM   orders o
WHERE  status != 'cancelled'
ORDER BY placed_at;

Ranking functions — ROW_NUMBER, RANK, DENSE_RANK

Three different answers to "which place did this row come in?" — and the difference only matters when there are ties. The diagram below uses the same input and ranks it three ways.

SELECT name,
       price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
       RANK()       OVER (ORDER BY price DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS drk
FROM   products;

-- Top-1 per category, breaking ties by name
SELECT category, name, price
FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY price DESC, name) AS rn
  FROM   products
) ranked
WHERE  rn = 1;

Running totals with SUM OVER

An aggregate combined with OVER (ORDER BY …) becomes a running aggregate — at each row, it sums everything from the start of the window up to and including that row. This is the bread-and-butter pattern for cumulative-revenue charts.

-- Per-day revenue, plus a running cumulative total
WITH daily AS (
  SELECT DATE(o.placed_at)               AS day,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM   orders o
  JOIN   order_items oi ON oi.order_id = o.id
  WHERE  o.status != 'cancelled'
  GROUP BY DATE(o.placed_at)
)
SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue
FROM   daily
ORDER BY day;