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.
| Function | Returns |
|---|---|
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 BYinside the function sorts the list;SEPARATORchanges the default comma.GROUP_CONCAT(DISTINCT col)removes duplicates.- The result is cut at
group_concat_max_lenbytes, 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
| Task | SQL |
|---|---|
| Count rows / non-NULL values | COUNT(*), COUNT(col) |
| Per group | SELECT k, COUNT(*) FROM t GROUP BY k |
| Filter groups | HAVING COUNT(*) > 1 |
| Count matching rows | SUM(condition) or COUNT(CASE WHEN ... THEN 1 END) |
| List values | GROUP_CONCAT(col ORDER BY col SEPARATOR ', ') |
| Subtotals | GROUP BY a, b WITH ROLLUP + GROUPING(a) |
| Any value of a column | ANY_VALUE(col) |
| Duplicates | GROUP 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?
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?
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?
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 aLEFT JOIN.- Every selected column must be grouped, aggregated or functionally dependent on the group key.
WHEREfilters rows before grouping,HAVINGfilters groups after.SUM(condition)counts matching rows;GROUP_CONCATbuilds lists but truncates at 1024 bytes by default.GROUP BYdoes not sort in MySQL 8+; addORDER 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.