Aggregation and Grouping

Aggregate functions

Questions such as "how many customers do we have?" or "what is our revenue?" are not about single rows but about many rows at once. An aggregate function takes the values of a column across a set of rows and returns one value. The five that every SQL database has are:

Without a GROUP BY, the whole table (after WHERE) is one group, so the query returns exactly one row. Here is a summary of the product catalog of the sample shop:

SELECT COUNT(*)   AS products,
       SUM(stock) AS units_in_stock,
       AVG(price) AS avg_price,
       MIN(price) AS cheapest,
       MAX(price) AS most_expensive
FROM products;

products  units_in_stock  avg_price  cheapest  most_expensive
--------  --------------  ---------  --------  --------------
10        528             65.9       7         249

Ten products, 528 units in stock, an average price of 65.9 euros, prices between 7 and 249. Note that AVG returns a fractional number even though price holds whole euros: SQLite computes averages as floating-point values (SQL Server would return a whole number here, because AVG of an int is an int). To make an average presentable, round it: ROUND(AVG(price), 2) rounds to two decimals (more on functions in Filtering, NULL and Functions).

COUNT(*), COUNT(column) and COUNT(DISTINCT)

The three forms of COUNT answer three different questions. In the customers table one customer, Priya, has no known city (city is NULL), and two customers live in Utrecht:

SELECT COUNT(*)             AS customers,
       COUNT(city)          AS with_city,
       COUNT(DISTINCT city) AS different_cities
FROM customers;

customers  with_city  different_cities
---------  ---------  ----------------
8          7          6

• COUNT(*) counts rows, whatever they contain: 8.
• COUNT(city) counts the rows where city is not NULL: 7.
• COUNT(DISTINCT city) counts the different non-NULL values: Utrecht only once, so 6.

How aggregates treat NULL

Every aggregate except COUNT(*) ignores NULL values. That is usually what you want (an unknown value should not count as zero), but it changes averages in a way that surprises people. This self-contained example stores three reviews, one without a star rating:

CREATE TABLE reviews (review_id INTEGER PRIMARY KEY, stars INTEGER);
INSERT INTO reviews (stars) VALUES (5), (4), (NULL);

SELECT COUNT(*)              AS reviews,
       COUNT(stars)          AS rated,
       AVG(stars)            AS avg_of_rated,
       SUM(stars) / 3.0      AS sum_over_all_rows
FROM reviews;

reviews  rated  avg_of_rated  sum_over_all_rows
-------  -----  ------------  -----------------
3        2      4.5           3.0

AVG(stars) is 9 / 2 = 4.5: the NULL row is not part of the average at all. If a missing rating should count as zero, say so explicitly with AVG(COALESCE(stars, 0)), which gives 3.0.

When there are no rows at all, COUNT returns 0 but the other aggregates return NULL — there is no sum of nothing:

SELECT COUNT(*)                 AS products,
       SUM(stock)               AS units,
       COALESCE(SUM(stock), 0)  AS units_or_zero
FROM products
WHERE category = 'Furniture';

products  units  units_or_zero
--------  -----  -------------
0         NULL   0

GROUP BY: one row per group

Usually you want a summary per something: products per category, orders per status, revenue per customer. GROUP BY splits the rows into groups that share the same value in the listed columns, and the query then returns one row per group, with the aggregates computed within each group.

SELECT category,
       COUNT(*)              AS products,
       MIN(price)            AS cheapest,
       MAX(price)            AS most_expensive,
       ROUND(AVG(price), 2)  AS avg_price
FROM products
GROUP BY category
ORDER BY category;

category     products  cheapest  most_expensive  avg_price
-----------  --------  --------  --------------  ---------
Accessories  2         35        45              40.0
Audio        1         129       129             129.0
Cables       3         7         12              9.33
Displays     1         249       249             249.0
Peripherals  3         25        89              57.67

Ten product rows became five category rows. You can group by several columns (GROUP BY country, city gives one row per combination), and NULL forms a group of its own: grouping customers by city gives one row for Priya's unknown city.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

city       customers
---------  ---------
Utrecht    2
NULL       1
Amsterdam  1
Antwerp    1
Cologne    1
Lisbon     1
Rotterdam  1

SQLite sorts NULL before every other value in ascending order, which is why it appears first among the cities with one customer. PostgreSQL and Oracle sort NULL last in ascending order; NULLS FIRST / NULLS LAST (PostgreSQL, Oracle, SQLite 3.30+) makes it explicit.

The rule: grouped or aggregated

After grouping, each output row stands for a whole group. A column that is neither in the GROUP BY nor inside an aggregate has several values in that group, and the database cannot know which one to show. Standard SQL therefore rejects such a query. In PostgreSQL:

-- Not valid standard SQL: which product name belongs to "Cables"?
SELECT category, name, COUNT(*)
FROM products
GROUP BY category;
-- PostgreSQL: ERROR:  column "products.name" must appear in the GROUP BY
--             clause or be used in an aggregate function

SQL Server and Oracle give a similar error, and so does MySQL 5.7.5 and later in its default mode (ONLY_FULL_GROUP_BY; MariaDB does not enable ONLY_FULL_GROUP_BY by default). PostgreSQL and MySQL also accept a column that depends on a grouped primary key, such as first_name when grouping by customer_id. SQLite accepts the query and silently takes name from one of the rows in the group — a so-called bare column. A query that runs is not necessarily a query that means something; write it the standard way so it works everywhere and says what you mean:

• if you want a value per group, aggregate it: MIN(name), MAX(price), COUNT(DISTINCT name);
• if you want the detail, add the column to the GROUP BY (and accept more, smaller groups);
• if you want "the most expensive product per category" with its name, that is a different question, solved with a subquery or a window function.

Revenue per order and per customer

The shop stores each order's lines in order_items: which product, how many, and the unit_price at the moment of ordering (prices in products may change later). A line is worth quantity * unit_price, and an aggregate can take an expression as well as a column:

SELECT order_id,
       COUNT(*)                     AS lines,
       SUM(quantity)                AS units,
       SUM(quantity * unit_price)   AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_id;

order_id  lines  units  order_total
--------  -----  -----  -----------
101       2      3      43
102       2      2      261
103       1      1      89
104       2      4      156
105       1      1      35
106       3      7      123
107       1      1      35
108       1      2      498
109       2      3      49
110       1      1      9

Revenue per customer needs the customer, which lives in orders (and the name in customers). That means joining the tables first — the Joins page explains JOIN in detail — and then grouping the joined rows by customer. Cancelled orders are left out:

SELECT c.customer_id,
       c.first_name,
       COUNT(DISTINCT o.order_id)        AS orders,
       SUM(oi.quantity * oi.unit_price)  AS revenue
FROM customers AS c
JOIN orders AS o       ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id   = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY c.customer_id, c.first_name
ORDER BY revenue DESC;

customer_id  first_name  orders  revenue
-----------  ----------  ------  -------
6            Priya       1       498
2            Kwame       2       296
3            Mei         2       205
1            Sanne       2       132
5            Lena        1       123
7            Tom         1       9

Grouping by c.customer_id (the key) rather than by the first name alone matters: two customers can share a first name, and they must not be merged into one group. c.first_name is in the GROUP BY too, so the query follows the grouping rule in every database. Omar (only a cancelled order) and Ana (no orders) do not appear, because the inner join and the WHERE remove them; the LEFT JOIN section shows how to keep them with a revenue of zero.

WHERE versus HAVING

Both clauses filter, but at different moments. SQL evaluates a query in a logical order that is different from the order in which you write it:

• WHERE runs before grouping. It sees individual rows and decides which rows go into the groups. It cannot use an aggregate, because no groups exist yet.
• HAVING runs after grouping. It sees one row per group and decides which groups stay. That is where conditions on COUNT, SUM and the other aggregates belong.

Filtering rows with WHERE

How many orders are still open (pending or shipped) per customer? The status is a property of each row, so it belongs in WHERE:

SELECT customer_id, COUNT(*) AS open_orders
FROM orders
WHERE status IN ('pending', 'shipped')
GROUP BY customer_id
ORDER BY customer_id;

customer_id  open_orders
-----------  -----------
2            1
3            1
6            1
7            1

Filtering groups with HAVING

Which orders are worth more than 100 euros? The total only exists after grouping, so the condition goes in HAVING:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING SUM(quantity * unit_price) > 100
ORDER BY order_id;

order_id  order_total
--------  -----------
102       261
104       156
106       123
108       498

Putting the same condition in WHERE fails, because aggregates are not allowed there:

SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
WHERE SUM(quantity * unit_price) > 100
GROUP BY order_id;

Error: misuse of aggregate: SUM()

A query can use both: WHERE to choose the rows, HAVING to choose the groups. Customers with at least two orders that were not cancelled:

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

customer_id  orders
-----------  ------
1            2
2            2
3            2

Ordering and ranking by aggregates

ORDER BY runs after SELECT, so it can sort by an aggregate, either by repeating the expression or by using its column alias. Combined with LIMIT it answers "top n" questions. The three best-selling products by units sold:

SELECT product_id,
       SUM(quantity)               AS units_sold,
       SUM(quantity * unit_price)  AS revenue
FROM order_items
GROUP BY product_id
ORDER BY units_sold DESC, product_id
LIMIT 3;

product_id  units_sold  revenue
----------  ----------  -------
1           6           54
3           4           100
10          4           28

The second sort key (product_id) breaks ties, so the result is the same every time. Without it, two products with the same total could come back in either order. SQL Server writes SELECT TOP 3 … instead of LIMIT 3; the standard form FETCH FIRST 3 ROWS ONLY works in PostgreSQL, Oracle and SQL Server (after OFFSET 0 ROWS).

A first look at window functions

GROUP BY collapses each group into one row, so the detail is gone. Sometimes you want both: every order line and the total of its order next to it. A window function computes a value over a set of related rows (the window, defined with OVER (...)) without collapsing them. PARTITION BY says which rows belong together; ORDER BY inside OVER gives them an order. SQLite supports window functions since version 3.25, as do PostgreSQL, MySQL 8.0, SQL Server and Oracle.

SELECT order_id,
       product_id,
       quantity * unit_price  AS line_total,
       SUM(quantity * unit_price) OVER (PARTITION BY order_id) AS order_total,
       ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY product_id) AS line_no
FROM order_items
WHERE order_id IN (101, 104, 106)
ORDER BY order_id, product_id;

order_id  product_id  line_total  order_total  line_no
--------  ----------  ----------  -----------  -------
101       1           18          43           1
101       3           25          43           2
104       1           27          156          1
104       8           129         156          2
106       3           50          123          1
106       7           45          123          2
106       10          28          123          3

All seven lines are still there. SUM(...) OVER (PARTITION BY order_id) adds up the lines of the same order, and ROW_NUMBER() numbers the lines within each order, starting again at 1 for each new order. Related functions are RANK() and DENSE_RANK() (which give ties the same number) and LAG() / LEAD() (the value from the previous or next row). Window functions are the usual tool for rankings, running totals and "the latest row per group"; they are a large subject on their own, used heavily in analytics.

Common mistake: counting after a join

Aggregates count and add whatever rows they are given. A join can repeat rows: an order with three lines appears three times after joining orders to order_items. Counting orders after such a join counts lines instead:

SELECT o.customer_id,
       COUNT(*)                   AS wrong_order_count,
       COUNT(DISTINCT o.order_id) AS order_count
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.customer_id IN (1, 3, 5)
GROUP BY o.customer_id
ORDER BY o.customer_id;

customer_id  wrong_order_count  order_count
-----------  -----------------  -----------
1            3                  2
3            4                  2
5            3                  1

Lena (customer 5) placed one order with three lines, so COUNT(*) says 3. COUNT(DISTINCT o.order_id) fixes the count, but there is no such fix for SUM: a value from the "one" side of the join (say, a shipping fee stored per order) would be added once per line. The reliable fix is to aggregate each table at its own level first (for example in a common table expression) and join the results. The Joins page shows how to spot duplicated rows.