Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 75 ยท Querying with MySQL

GROUP BY, HAVING and Aggregates in MySQL

Master GROUP BY in MySQL: ONLY_FULL_GROUP_BY, ANY_VALUE, GROUP_CONCAT with ORDER BY and SEPARATOR, WITH ROLLUP subtotals and how aggregates treat NULL.

Aggregates turn many rows into one answer: how many orders, what total, which average. GROUP BY does that once per group, so you get one row per customer, per month or per category. MySQL follows standard SQL here, with a few of its own twists: a strict mode that rejects ambiguous queries, a function (GROUP_CONCAT) that glues values into a string, a WITH ROLLUP modifier that adds subtotals, and a handful of rules about NULL that decide whether your totals are right.

If GROUP BY is new to you, the general SQL tutorial explains it from scratch. This lesson concentrates on how MySQL treats it.

Group, then summarize

Group Rows with the same key fall into one bucket.
Aggregate COUNT, SUM, AVG, MIN, MAX per bucket.
HAVING Filter the buckets after they are built.

WHERE filters rows before grouping. HAVING filters groups after.

1 / 2

A quick refresher

An aggregate query has three clauses worth keeping apart: WHERE removes rows before grouping, GROUP BY forms the groups, and HAVING removes groups after the aggregates are computed. The next example uses a small sales table that every later example reuses:

SQL WHERE before grouping, HAVING after

Press Run (or Ctrl + Enter) to see the result here.

North has two rows left after the lamp is filtered out (a total of 750), South has two rows but only 200 in total, because one amount is NULL, and East's 150 is dropped by HAVING. In the result, only North passes the filter.

ONLY_FULL_GROUP_BY and ANY_VALUE

Consider this query:

SQL
SELECT region, rep, SUM(amount)
FROM sales
GROUP BY region;

Each group (a region) can contain several reps, so which rep should the result show? Older MySQL quietly picked one at random. Since 5.7 the SQL mode ONLY_FULL_GROUP_BY is on by default, and MySQL refuses the query:

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

Every column in the select list must be one of: part of GROUP BY, inside an aggregate function, or functionally dependent on the grouped columns. The last rule is the clever one: if you group by a table's primary key, MySQL knows every other column of that table is determined, so GROUP BY customers.id may select customers.name without grouping by it.

The fixes, in order of preference:

  1. Add the column to GROUP BY if you really want one row per (region, rep).
  2. Aggregate it: MAX(rep) or COUNT(DISTINCT rep).
  3. Wrap it in ANY_VALUE() when you know all values in the group are the same and you do not care which one is returned: ANY_VALUE(rep).

ANY_VALUE tells MySQL "I accept an arbitrary value from the group" and turns off the check for that expression. Use it sparingly and only when the column truly cannot differ. Turning off ONLY_FULL_GROUP_BY for the whole server hides real bugs and is not recommended (SQL Modes and Strictness).

SQLite allows the ambiguous form (it returns a value from an arbitrary row), so a live example cannot reproduce error 1055. Treat the query above as a MySQL-only lesson.

Aggregates and NULL

Aggregate functions skip NULL inputs, with one exception, and this is the root of many wrong reports:

Expression What it does with NULL
COUNT(*) Counts rows. NULLs are irrelevant.
COUNT(column) Counts rows where the column is not NULL.
SUM, AVG, MIN, MAX Ignore NULLs.
SUM over zero rows (or all NULL) Returns NULL, not 0.
COUNT over zero rows Returns 0.

AVG(amount) divides by the number of non-NULL rows, not by the total number of rows. If a missing value should count as zero, say so with COALESCE(amount, 0):

SQL NULLs inside aggregates

Press Run (or Ctrl + Enter) to see the result here.

For North, COUNT(*) is 3, COUNT(amount) is 2, the total is 400, AVG ignoring NULL is 200 and the average with NULL as zero is about 133.33. South has a NULL total, because all its inputs are NULL. The query on the missing region returns one row with NULL and 0.

GROUP_CONCAT

GROUP_CONCAT joins the values of a group into a single string. It accepts DISTINCT, an ORDER BY inside the parentheses and a SEPARATOR:

SQL
SELECT region,
       GROUP_CONCAT(DISTINCT rep ORDER BY rep SEPARATOR ', ') AS reps
FROM sales
GROUP BY region;

Things to know:

  • The default separator is a comma with no space.
  • NULL values are skipped. If every value is NULL, the result is NULL.
  • The result is cut off at group_concat_max_len, which defaults to 1,024 bytes. Truncation produces only a warning, so long lists silently lose their tail. Raise it with SET SESSION group_concat_max_len = 1000000; when you need to.
  • Inside GROUP_CONCAT the order is whatever the rows arrive in unless you give an ORDER BY. Always give one when the order matters.
  • It is a presentation tool. If you need structured data, return the rows and group them in the application, or use JSON_ARRAYAGG (The JSON Data Type).

SQLite has group_concat as well, with a slightly different shape: the separator is the second argument, and an ORDER BY clause inside the call is accepted in recent versions. The idea is the same:

SQL Gluing group members into a string

Press Run (or Ctrl + Enter) to see the result here.

The first query lists each rep once per region, joined with commas. The second one sorts the names alphabetically before joining and repeats Ana twice for North, since it has no DISTINCT. MySQL's own version of the second query would use SEPARATOR ' | ' and write ORDER BY rep before it.

WITH ROLLUP

Reports often need subtotals and a grand total. WITH ROLLUP adds them to a GROUP BY as extra rows, where the column being summed over shows NULL:

SQL
SELECT region, product, SUM(amount) AS total
FROM sales
GROUP BY region, product WITH ROLLUP;

For two grouping columns you get detail rows, one subtotal per region (with product set to NULL), and one grand-total row (both NULL). The problem is that a real NULL in the data looks the same as a rollup row. The GROUPING() function tells them apart, returning 1 for a rollup NULL and 0 for a real value:

SQL
SELECT
  IF(GROUPING(region),  'ALL REGIONS',  region)  AS region,
  IF(GROUPING(product), 'ALL PRODUCTS', product) AS product,
  SUM(amount) AS total
FROM sales
GROUP BY region, product WITH ROLLUP;

ORDER BY can be combined with WITH ROLLUP since 8.0.12, but sorting by an aggregate can push subtotal rows out of place, so check the output. SQLite has no WITH ROLLUP, but you can rebuild the same shape with UNION ALL, which also shows what the modifier does under the hood:

SQL Subtotals and a grand total (ROLLUP emulated)

Press Run (or Ctrl + Enter) to see the result here.

The result lists detail rows first, then one subtotal per region (East 150, North 830, South 320), then the grand total 1,300. In MySQL, GROUP BY region, product WITH ROLLUP produces the same rows in one pass over the data, with NULL where this query writes labels.

Good habits

  • Keep ONLY_FULL_GROUP_BY on. It turns silent wrong answers into errors.
  • Filter with WHERE whenever you can, and use HAVING only for conditions on aggregates. WHERE runs before grouping and can use indexes.
  • Remember that COUNT(column) is not COUNT(*). State clearly which one you mean.
  • Use COALESCE(SUM(x), 0) when a report must show 0 instead of NULL.
  • Treat GROUP_CONCAT output as display text and watch group_concat_max_len.
  • Grouping by an expression is fine, but write it once in the select list and repeat it in GROUP BY (or use the alias, which MySQL allows in GROUP BY and HAVING).

What ran live here

Grouping, HAVING, NULL handling, group_concat and a UNION ALL version of rollup ran in SQLite. ONLY_FULL_GROUP_BY errors, ANY_VALUE, MySQL's GROUP_CONCAT syntax with SEPARATOR, GROUPING() and WITH ROLLUP are MySQL server features and are shown as read-only code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does ONLY_FULL_GROUP_BY do?

    The mode rejects ambiguous queries. Columns that are grouped, aggregated or functionally dependent on the grouped columns are allowed.
  2. 2. A table has 5 rows and the column score is NULL in 2 of them. What does COUNT(score) return?

    COUNT(column) counts only non-NULL values. COUNT(*) would return 5.
  3. 3. What is the default limit on the length of a GROUP_CONCAT result?

    group_concat_max_len defaults to 1,024 bytes. Longer results are truncated with a warning, not an error.
  4. 4. In a WITH ROLLUP result, how can you tell a subtotal row from a row whose value is really NULL?

    GROUPING(column) returns 1 for rows produced by the rollup and 0 for ordinary rows, even when the data itself contains NULL.
  5. 5. What does SUM(amount) return when no rows match the WHERE clause?

    SUM over no rows is NULL, unlike COUNT, which gives 0. Use COALESCE(SUM(amount), 0) when you need a zero.

Summary

  • WHERE filters rows before grouping, HAVING filters groups after aggregation.
  • ONLY_FULL_GROUP_BY (on by default) requires every selected column to be grouped, aggregated or functionally dependent. ANY_VALUE() is the explicit escape hatch.
  • Aggregates skip NULL. COUNT(*) counts rows, COUNT(col) counts non-NULL values, and SUM of nothing is NULL.
  • GROUP_CONCAT supports DISTINCT, ORDER BY and SEPARATOR, and truncates at 1,024 bytes by default.
  • WITH ROLLUP adds subtotals and a grand total. GROUPING() separates them from real NULL values.

Next: Joins in MySQL.