MySQL – GROUP BY and HAVING

GROUP BY collapses many rows into one per group, and the aggregate functions decide what that one row says. It is how you turn a table of orders into a report.

The aggregates

SELECT status, COUNT(*) AS orders, ROUND(SUM(total), 2) AS revenue, ROUND(AVG(total), 2) AS avg_order
FROM   customer_order GROUP BY status ORDER BY orders DESC, status;

+-----------------+--------+---------+-----------+
| status          | orders | revenue | avg_order |
+-----------------+--------+---------+-----------+
| COMPLETED       |     13 |  357.40 |     27.49 |
| CANCELLED       |      2 |   39.23 |     19.62 |
| PAID            |      1 |   27.02 |     27.02 |
| PENDING_PAYMENT |      1 |   11.92 |     11.92 |
| PREPARING       |      1 |   40.86 |     40.86 |
+-----------------+--------+---------+-----------+

COUNT, SUM, AVG, MIN, MAX — plus GROUP_CONCAT, which joins a group's values into one string. Note the ORDER BY: MySQL 8 does not sort groups implicitly. Without it the order is undefined, however sorted it happens to look.

COUNT(*) versus COUNT(column)

SELECT COUNT(*) AS rows_, COUNT(user_id) AS with_account, COUNT(DISTINCT user_id) AS distinct_accounts
FROM   customer_order;

+-------+--------------+-------------------+
| rows_ | with_account | distinct_accounts |
+-------+--------------+-------------------+
|    18 |            8 |                 1 |
+-------+--------------+-------------------+

Three different questions:

• COUNT(*) counts rows.
• COUNT(column) counts non-NULL values — ten orders are guest orders with a NULL user_id.
• COUNT(DISTINCT column) counts distinct non-NULL values.

Every aggregate ignores NULLs, and for AVG that changes the answer: the denominator is the count of non-NULL values, not the row count. An aggregate over zero rows returns NULL rather than 0, which is why COALESCE(SUM(x), 0) is a sensible habit. See NULL and IS NULL.

HAVING versus WHERE

SELECT i.product_name, SUM(i.quantity) AS units, ROUND(SUM(i.line_total), 2) AS revenue
FROM   order_item i
GROUP  BY i.product_name
HAVING SUM(i.quantity) >= 3
ORDER  BY units DESC, i.product_name;

+-------------------+-------+---------+
| product_name      | units | revenue |
+-------------------+-------+---------+
| Pepperoni Pizza   |     6 |   98.69 |
| Pepsi             |     6 |   17.44 |
| Cheese Pizza      |     3 |   35.97 |
| Meat Lovers Pizza |     3 |   64.47 |
| Supreme Pizza     |     3 |   55.72 |
+-------------------+-------+---------+

They are not interchangeable, and the difference falls out of when each one runs:

• WHERE filters rows before grouping. It cannot see an aggregate, because none has been computed yet.
• HAVING filters groups after. It can see aggregates.

When a condition could go in either — one on a plain column — put it in WHERE. Filtering before grouping means fewer rows to group and lets an index help; filtering after means doing the work and then discarding it.

MySQL lets HAVING reference a select-list alias (HAVING units >= 3 works), because HAVING runs after SELECT is evaluated. WHERE cannot — see SELECT.

ONLY_FULL_GROUP_BY

This is the MySQL 8 change that breaks more upgrades than any other:

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

The query is genuinely meaningless: thirteen orders are COMPLETED with different customer names, so there is no single customer_name for that group. MySQL 5.7 and earlier picked one arbitrarily and said nothing, which is how wrong numbers ended up in reports for years.

MySQL 8 turns ONLY_FULL_GROUP_BY on by default and rejects it. The fixes, in order of preference:

• Add the column to GROUP BY — if you meant to group by both.
• Aggregate it: MAX(customer_name), or GROUP_CONCAT to see them all.
• Use ANY_VALUE(customer_name) to say explicitly "any one will do" — which documents that you knew.

Do not turn the mode off. It is catching a real ambiguity, and the errors are a list of queries that were already returning arbitrary answers.

There is one legitimate exception the server understands: grouping by a primary key. GROUP BY o.id lets you select any column of o, because the key functionally determines them — one row per group, no ambiguity.

Grouping by several columns, and WITH ROLLUP

SELECT COALESCE(order_type, 'ALL') AS order_type, COUNT(*) AS orders
FROM   customer_order GROUP BY order_type WITH ROLLUP;

+------------+--------+
| order_type | orders |
+------------+--------+
| CARRYOUT   |      7 |
| DELIVERY   |     11 |
| ALL        |     18 |
+------------+--------+

WITH ROLLUP adds subtotal rows. It marks them by putting NULL in the grouped column, which is why the COALESCE is there — and also why it is ambiguous if the column can genuinely be NULL. GROUPING(order_type) distinguishes the two properly.

WITH ROLLUP cannot be combined with ORDER BY, since the subtotal rows have to stay attached to the groups they summarise.

Watch the join

The trap that produces wrong totals: aggregate a column from the "one" side across a join to the "many" side, and every value is counted once per matching row.

-- WRONG: an order's total is counted once per line item it has
SELECT ROUND(SUM(o.total), 2) FROM customer_order o JOIN order_item i ON i.order_id = o.id;

-- right: aggregate the detail table, or aggregate before joining
SELECT ROUND(SUM(i.line_total), 2) FROM order_item i;

Nothing warns you — the number is simply too big. It is the same fan-out problem described in INNER JOIN, and the general fix is to aggregate in a subquery or CTE and join to the result.

What to remember

• Add ORDER BY — grouped output has no guaranteed order.
• COUNT(*) counts rows; COUNT(col) counts non-NULL values.
• WHERE before grouping, HAVING after. Prefer WHERE.
• ONLY_FULL_GROUP_BY is on by default and is right. Fix the query.
• Do not SUM the "one" side across a join to the "many" side.