Lesson 09 intermediate

Grouping and Aggregate Functions

Summarise data for Orbit's reports: COUNT, SUM, AVG, MIN and MAX, the difference between COUNT(*) and COUNT(col), GROUP BY and the ONLY_FULL_GROUP_BY rule, HAVING versus WHERE, conditional aggregation, GROUP_CONCAT to fold rows into lists, subtotals with WITH ROLLUP and GROUPING(), and grouping by month.

Lists of rows answer “what is there?”. Reports answer “how many, how much, per what?”. This lesson turns Orbit’s notes into the numbers its dashboard shows.

Aggregate functions

An aggregate function turns many rows into one value:

SELECT COUNT(*) AS notes,
       SUM(pinned) AS pinned,
       MIN(created_at) AS first,
       MAX(created_at) AS last
FROM notes;
+-------+--------+---------------------+---------------------+
| notes | pinned | first               | last                |
+-------+--------+---------------------+---------------------+
|     5 |      2 | 2026-10-01 09:00:00 | 2026-11-02 10:00:00 |
+-------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

SUM(pinned) counts pinned notes because pinned is 0 or 1, a handy MySQL idiom.

FunctionReturns
COUNT(*)Number of rows
COUNT(col)Number of rows where col is not NULL
COUNT(DISTINCT col)Number of different non-NULL values
SUM(col), AVG(col)Total and mean, ignoring NULLs
MIN(col), MAX(col)Smallest and largest value; works on text and dates too
GROUP_CONCAT(col)Values joined into one string
JSON_ARRAYAGG(col), JSON_OBJECTAGG(k, v)Values collected into JSON (lesson 17)

On an empty set, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. Wrap them in IFNULL(SUM(x), 0) when your report needs a number.

GROUP BY

GROUP BY splits the rows into groups and computes the aggregates once per group:

SELECT user_id, COUNT(*) AS notes, SUM(pinned) AS pinned
FROM notes
GROUP BY user_id
ORDER BY user_id;
+---------+-------+--------+
| user_id | notes | pinned |
+---------+-------+--------+
|       1 |     3 |      1 |
|       2 |     2 |      1 |
+---------+-------+--------+
2 rows in set (0.00 sec)

Note: Since MySQL 8.0, GROUP BY no longer sorts its result. Older applications that relied on the implicit sort must add ORDER BY.

Counting with a LEFT JOIN

To include users with zero notes, start from users and left join. Then the choice of what to count matters:

SELECT u.name,
       COUNT(*)    AS count_star,
       COUNT(n.id) AS notes
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.id;
+------------+------------+-------+
| name       | count_star | notes |
+------------+------------+-------+
| Ana Silva  |          3 |     3 |
| Ben Carter |          2 |     2 |
| Chen Wei   |          1 |     0 |
+------------+------------+-------+
3 rows in set (0.00 sec)

Chen’s group contains one row, the user with NULL note columns, so COUNT(*) says 1. COUNT(n.id) skips the NULL and correctly says 0.

The ONLY_FULL_GROUP_BY rule

What would this query show in the title column?

SELECT user_id, title, COUNT(*) FROM notes GROUP BY user_id;

Ana has three notes, so there is no single answer. MySQL’s default ONLY_FULL_GROUP_BY mode refuses the query:

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

Every column in SELECT must be grouped, aggregated, or functionally dependent on the grouped columns. The last case is why GROUP BY u.id lets you select u.name and u.email too: they are determined by the primary key.

Fix the query by saying what you mean: MAX(title), GROUP_CONCAT(title), or, if any value will do, ANY_VALUE(title). Do not switch the mode off; old MySQL versions returned an arbitrary row’s value here, and the bugs it caused were hard to find.

Filtering groups with HAVING

WHERE filters rows before grouping; HAVING filters groups after:

SELECT user_id, COUNT(*) AS notes
FROM notes
WHERE created_at >= '2026-10-01'
GROUP BY user_id
HAVING COUNT(*) >= 3;
+---------+-------+
| user_id | notes |
+---------+-------+
|       1 |     3 |
+---------+-------+
1 row in set (0.00 sec)

Put every condition that does not need an aggregate in WHERE: rows removed early never have to be grouped. MySQL allows HAVING notes >= 3 using the alias, but HAVING COUNT(*) >= 3 is portable.

Conditional aggregation

Aggregate only the rows that match a condition by putting the condition inside the function. One query gives several counts per user:

SELECT user_id,
       COUNT(*) AS total,
       SUM(pinned) AS pinned,
       SUM(created_at >= '2026-11-01') AS this_month
FROM notes
GROUP BY user_id
ORDER BY user_id;
+---------+-------+--------+------------+
| user_id | total | pinned | this_month |
+---------+-------+--------+------------+
|       1 |     3 |      1 |          1 |
|       2 |     2 |      1 |          0 |
+---------+-------+--------+------------+
2 rows in set (0.00 sec)

A comparison in MySQL evaluates to 1 or 0, so SUM(condition) counts matching rows. The portable spelling is COUNT(CASE WHEN condition THEN 1 END).

GROUP_CONCAT: rows into a list

Lesson 8’s tag query returned one row per note and tag. GROUP_CONCAT folds the tags back into one row per note:

SELECT n.title,
       GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM notes n
JOIN note_tags nt ON nt.note_id = n.id
JOIN tags t ON t.id = nt.tag_id
GROUP BY n.id, n.title
ORDER BY n.id;
+------------------+-----------------+
| title            | tags            |
+------------------+-----------------+
| Shopping list    | personal        |
| Deploy checklist | work            |
| Book ideas       | personal        |
| MySQL tips       | databases, work |
| Meeting notes    | work            |
+------------------+-----------------+
5 rows in set (0.00 sec)
  • ORDER BY inside the function sorts the list; SEPARATOR changes the default comma.
  • GROUP_CONCAT(DISTINCT col) removes duplicates.
  • The result is cut at group_concat_max_len bytes, 1024 by default, with only a warning. Raise it (SET SESSION group_concat_max_len = 100000;) before concatenating long lists.

How many notes carry each tag, most used first:

SELECT t.name, COUNT(*) AS notes
FROM tags t
JOIN note_tags nt ON nt.tag_id = t.id
GROUP BY t.id, t.name
ORDER BY notes DESC, t.name;
+-----------+-------+
| name      | notes |
+-----------+-------+
| work      |     3 |
| personal  |     2 |
| databases |     1 |
+-----------+-------+
3 rows in set (0.00 sec)

Subtotals with WITH ROLLUP

WITH ROLLUP adds summary rows: one per higher grouping level plus a grand total. The grouping columns are NULL on those rows, and GROUPING(col) returns 1 there so you can label them:

SELECT IF(GROUPING(u.plan), 'all plans', u.plan) AS plan,
       COUNT(n.id) AS notes
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
GROUP BY u.plan WITH ROLLUP
ORDER BY GROUPING(u.plan), u.plan;
+-----------+-------+
| plan      | notes |
+-----------+-------+
| free      |     3 |
| pro       |     2 |
| all plans |     5 |
+-----------+-------+
3 rows in set (0.00 sec)

ORDER BY together with WITH ROLLUP and the GROUPING() function need MySQL 8.0.12 or later. Group by two columns, such as GROUP BY plan, user_id WITH ROLLUP, and you get a subtotal per plan plus the grand total.

Grouping by time

Dashboards love “per month”. Group by a formatted date:

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS notes
FROM notes
GROUP BY month
ORDER BY month;
+---------+-------+
| month   | notes |
+---------+-------+
| 2026-10 |     4 |
| 2026-11 |     1 |
+---------+-------+
2 rows in set (0.00 sec)

Months with no notes simply do not appear. When a chart needs every month, generate the calendar with a recursive CTE (lesson 10) and left join the counts to it.

Quick reference

TaskSQL
Count rows / non-NULL valuesCOUNT(*), COUNT(col)
Per groupSELECT k, COUNT(*) FROM t GROUP BY k
Filter groupsHAVING COUNT(*) > 1
Count matching rowsSUM(condition) or COUNT(CASE WHEN ... THEN 1 END)
List valuesGROUP_CONCAT(col ORDER BY col SEPARATOR ', ')
SubtotalsGROUP BY a, b WITH ROLLUP + GROUPING(a)
Any value of a columnANY_VALUE(col)
DuplicatesGROUP BY col HAVING COUNT(*) > 1

Exercise

A LEFT JOIN from users to notes groups by user. Which expression correctly shows 0 for users without notes?

  1. COUNT(n.id)
  2. COUNT(*)
  3. COUNT(u.id)
  4. SUM(1)
Show answer

Answer: A. Users without notes still produce one row with NULL note columns. COUNT(*), COUNT(u.id) and SUM(1) count that row; COUNT(n.id) skips the NULL.

Exercise

Why does SELECT user_id, title, COUNT(*) FROM notes GROUP BY user_id fail with error 1055?

  1. COUNT(*) cannot be combined with other columns
  2. title is neither grouped, aggregated nor determined by user_id
  3. GROUP BY requires an ORDER BY
  4. The column alias for COUNT(*) is missing
Show answer

Answer: B. ONLY_FULL_GROUP_BY rejects columns that could have several values within a group. Aggregate title, add it to GROUP BY or use ANY_VALUE(title).

Exercise

A report concatenates thousands of email addresses with GROUP_CONCAT and the result stops mid-address. What is the cause?

  1. GROUP_CONCAT only accepts up to 100 rows
  2. The result column is VARCHAR(255)
  3. The separator must be escaped
  4. The result exceeded group_concat_max_len, 1024 bytes by default
Show answer

Answer: D. GROUP_CONCAT silently truncates at group_concat_max_len and only raises a warning. Increase the session variable before running the query.

Key takeaways

  • COUNT(*) counts rows; COUNT(col) skips NULLs, which matters after a LEFT JOIN.
  • Every selected column must be grouped, aggregated or functionally dependent on the group key.
  • WHERE filters rows before grouping, HAVING filters groups after.
  • SUM(condition) counts matching rows; GROUP_CONCAT builds lists but truncates at 1024 bytes by default.
  • GROUP BY does not sort in MySQL 8+; add ORDER BY.

FAQ

How do I find duplicate emails?

Group by the column and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;. A unique key prevents them in the first place.

Can I count distinct combinations of two columns?

Yes: COUNT(DISTINCT user_id, pinned) counts distinct pairs, ignoring rows where any of the columns is NULL.

Is COUNT(1) faster than COUNT(*)?

No. InnoDB treats them identically. Use COUNT(*), which states the intent “count rows”.

Why is SELECT COUNT(*) FROM a big table slow?

InnoDB does not store an exact row count, because different transactions may see different numbers of rows. It scans the smallest index to count. For a quick estimate use information_schema.TABLES.TABLE_ROWS, or keep a counter table if you need exact counts often.

↑ ↓ navigate Enter open Esc close