GROUP BY, aggregates and HAVING

Game: chapters IV, VI and VII

So far every query returned rows of the table. Aggregates return facts about many rows at once: how many, how much, the largest, the average.

Aggregate functions

Five functions do most of the work: COUNT, SUM, AVG, MIN and MAX. Used without grouping, each one collapses the whole result into a single row. COUNT(*) counts rows; COUNT(column) counts the rows where that column is not NULL; COUNT(DISTINCT column) counts the different values. The examples run on the bakery database from the bakery database page.

SELECT COUNT(*) AS products,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       ROUND(AVG(price), 2) AS average
FROM product;

One row per group: GROUP BY

GROUP BY splits the rows into groups that share the same value in one or more columns, then computes each aggregate once per group. The result has one row per group. Picture the database sorting the rows into piles, one pile per category, and then counting or adding up each pile separately.

SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS average_price
FROM product
GROUP BY category
ORDER BY category;

The rule to remember: every column in SELECT should either appear in GROUP BY or sit inside an aggregate. Here category is the grouping column and the other two are aggregates. If you add name to the list, the question no longer makes sense (which name, out of six breads?), and the query is refused: the next section shows how.

Grammar: grouped or aggregated

A column in SELECT that is neither grouped nor inside an aggregate has several values inside one group, and no single one of them to show, so the query is refused.

A query with a red edge is one the game's terminal refuses: the answer under it is the terminal's own, and the query after it is the correct version.

SELECT category, name, COUNT(*)
FROM product
GROUP BY category;

SELECT category, COUNT(*) AS products, MIN(name) AS first_alphabetically
FROM product
GROUP BY category
ORDER BY category;

The same rule applies without GROUP BY: an aggregate turns the whole table into one row, so SELECT category, COUNT(*) FROM product is wrong too, and so is SELECT * with GROUP BY, unless you group by the primary key. That is the one exception: when you group by a table's primary key, every other column of that table has a single value per group, so you may select them. Here it counts each customer's orders; LIMIT 5 keeps the five most frequent, and the cut is clean: the fifth has 17 orders, the sixth 16. Had they tied, c.id would pick one of them and silently drop the other.

SELECT c.id, c.name, COUNT(*) AS orders
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY orders DESC, c.id
LIMIT 5;

Grouping by more than one column

Grouping by two columns makes one group per combination that actually occurs. Counting orders per customer and per day, for instance, gives one row for each customer on each day they came in. A day on which a customer did not come in produces no row at all: groups are built from the rows that exist, never from the ones that might have.

Filtering groups: HAVING

WHERE filters rows before they are grouped. HAVING filters groups after they are counted, so it is the place for conditions on an aggregate, such as "customers with at least fifteen orders". You cannot write WHERE COUNT(*) >= 15, because when WHERE runs, the counting has not happened yet.

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 15
ORDER BY orders DESC, customer_id;

Both clauses can appear in the same query, each doing its own job. The next example keeps only the orders taken before nine (a row filter, so WHERE), groups them by day, and keeps the days with at least five such early orders (a group filter, so HAVING): six of the twenty-four opening days.

SELECT day, COUNT(*) AS early_orders
FROM orders
WHERE time < '09:00'
GROUP BY day
HAVING COUNT(*) >= 5
ORDER BY day;

Grammar: HAVING without GROUP BY

HAVING filters groups. Without GROUP BY, the whole table is one group, so only aggregates make sense in it: a plain column has no single value.

SELECT category
FROM product
HAVING COUNT(*) > 6;

SELECT category
FROM product
GROUP BY category
HAVING COUNT(*) > 6
ORDER BY category;

Bread, with six products, is left out. A query that only selects aggregates may keep its HAVING without GROUP BY: it returns the single row of the whole table, or nothing.

SELECT COUNT(*) AS products
FROM product
HAVING COUNT(*) > 3;

Adding up quantities

SUM adds the values of a column within each group. In the bakery, the order_item table says how many of each product went into each order, so summing quantity per product gives the number of pieces sold over the four weeks. Sorting by the sum shows the best sellers first; HAVING keeps only the products with at least 58 pieces, and lines counts the order lines behind each total. Four products tie at 58, the croissant and the cinnamon roll among them, and product_id breaks the tie, so the order is fixed. To turn quantities into money, multiply each line by its unit_price, the price paid on that line: SUM(quantity * unit_price). The catalogue's product.price is today's price, and three products cost less before Monday 16 March, so it would overstate the early takings; the JOIN page brings in the names and both prices.

SELECT product_id, SUM(quantity) AS pieces, COUNT(*) AS lines
FROM order_item
GROUP BY product_id
HAVING SUM(quantity) >= 58
ORDER BY pieces DESC, product_id;

Grammar: ORDER BY after GROUP BY

Sorting happens after grouping, so ORDER BY follows the same rule as SELECT: a grouped column, an aggregate, or an alias from the SELECT list.

SELECT category, COUNT(*) AS products
FROM product
GROUP BY category
ORDER BY price DESC;

SELECT category, COUNT(*) AS products
FROM product
GROUP BY category
ORDER BY MAX(price) DESC;

Say which price you mean: the dearest of each category here, or MIN(price), or AVG(price).

Grammar: string_agg and SUM

To join the texts of a group into one, standard SQL writes string_agg(column, ', '), with an ORDER BY inside the parentheses to fix the order of the pieces. GROUP_CONCAT is not standard SQL, and neither is TOTAL(x): write SUM(x), or COALESCE(SUM(x), 0) when an empty group should read 0.

SELECT category, GROUP_CONCAT(name)
FROM product
GROUP BY category;

SELECT category, string_agg(name, ', ' ORDER BY name) AS products
FROM product
GROUP BY category
ORDER BY category;

The order in which a query runs

It helps to know the logical order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. Each step only sees what the previous one produced. That order explains most of the rules on this page; Order of execution goes through it step by step.

Common mistakes

Putting an aggregate condition in WHERE instead of HAVING. Putting a plain row condition in HAVING: it may work, but it filters late and reads badly. Selecting a column that is neither grouped nor aggregated, which has no single value to show (see Grammar: grouped or aggregated). Using COUNT(column) when you meant COUNT(*) and losing the rows where the column is NULL. Expecting a group for something that has no rows: a product that was never ordered, such as the Pistachio financier, simply does not appear when you group order_item.

Try it: The counter by category, Orders by town, Takings per day and Busy hours, on the exercises page.

In the game: Chapter IV, The Cab's Week, practises GROUP BY and HAVING; Chapter VI, Follow the Francs, adds SUM over several joined tables; Chapter VII, The Quietest Hour, groups by a CASE label.