Database NotesRohit’s interview study guide
/
Chapter 04

Aggregation and Grouping

Collapsing many rows into summaries: aggregate functions, GROUP BY, HAVING, MySQL's ONLY_FULL_GROUP_BY rule, subtotals with WITH ROLLUP, string and JSON aggregation, and pivots with conditional aggregation.

8 topics
Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#Aggregate functions

Added

Every query you've seen so far returns one output row per input row (or per joined row). An aggregate function does the opposite: it reads a whole set of rows and returns one value. COUNT counts them, SUM adds them up, AVG averages them, MIN and MAX find the extremes. Reports, dashboards, "how many", "how much", "what's the latest" — all of it is aggregation.

A good mental model is a funnel. Rows pour in at the top, the aggregate keeps a tiny running state (a counter, a running total, the smallest value seen so far), and when the last row has gone through, one number comes out at the bottom. Without GROUP BY there is exactly one funnel for the whole table, so you always get exactly one row back — even if no rows went in.

Two rules explain almost every surprise: aggregates skip NULL (only COUNT(*) counts rows regardless of their contents), and over an empty set, COUNT returns 0 but everything else returns NULL.

SQL
SELECT COUNT(*)      AS employees,
       SUM(salary)   AS payroll,
       AVG(salary)   AS avg_salary,
       MIN(salary)   AS lowest,
       MAX(salary)   AS highest,
       MIN(hired_on) AS first_hire,
       MAX(hired_on) AS latest_hire
FROM employees;
Result
+-----------+------------+---------------+----------+-----------+------------+-------------+
| employees | payroll    | avg_salary    | lowest   | highest   | first_hire | latest_hire |
+-----------+------------+---------------+----------+-----------+------------+-------------+
|        10 | 1162000.00 | 116200.000000 | 72000.00 | 185000.00 | 2018-03-01 | 2025-06-16  |
+-----------+------------+---------------+----------+-----------+------------+-------------+
1 row in set (0.000 sec)
What’s happening
  1. There's no WHERE and no GROUP BY, so all 10 rows of employees go into one funnel and the query returns exactly one row.
  2. COUNT(*) counts rows: 10. SUM(salary) adds the ten salaries: 1162000.00 — the result keeps the column's DECIMAL(10,2) scale, so money stays exact.
  3. AVG(salary) is SUM / COUNT = 1162000 / 10 = 116200. It prints as 116200.000000: for exact (DECIMAL/integer) input MySQL returns a DECIMAL with extra scale (the column's 2 decimals plus div_precision_increment, which is 4 by default).
  4. MIN and MAX work on anything that can be ordered, not just numbers: on hired_on they return the earliest (2018-03-01, Asha) and the latest (2025-06-16, Jonas) dates. On strings they use the column's collation.
  5. Notice what's missing: the answer has no employee names. An aggregate returns a value about the set, not "the row with the highest salary". Getting the row behind MAX is a different problem (see Top N per group).
AdvancedEdge case: aggregates over no rows

What happens when the funnel is empty? Research (department 4) has no employees:

SQL
SELECT COUNT(*), SUM(salary), AVG(salary), MAX(salary)
FROM employees
WHERE department_id = 4;
Result
+----------+-------------+-------------+-------------+
| COUNT(*) | SUM(salary) | AVG(salary) | MAX(salary) |
+----------+-------------+-------------+-------------+
|        0 |        NULL |        NULL |        NULL |
+----------+-------------+-------------+-------------+
1 row in set (0.000 sec)
What’s happening
  1. WHERE department_id = 4 matches no rows, so nothing reaches the aggregates.
  2. You still get one row. An aggregate query without GROUP BY always returns one row; it's the "summary of nothing".
  3. COUNT(*) is 0 — counting nothing gives zero.
  4. SUM, AVG and MAX are NULL, not 0. The SQL standard reasons that the sum of no values is unknown, not zero. Your application code (or report) then shows a blank or crashes on null.
  5. The fix when you want a zero is COALESCE(SUM(salary), 0). Do that only when zero is really the right answer: an average of no salaries is not zero.
AdvancedHow NULLs change an average

Aggregates skip NULL inputs, which changes averages. Here's a tiny inline table (built with VALUES, available since MySQL 8.0.19) of review scores where one reviewer didn't give a score:

SQL
SELECT COUNT(*)            AS reviews,
       COUNT(score)        AS scored,
       SUM(score)          AS total,
       AVG(score)          AS avg_skipping_null,
       SUM(score) / COUNT(*) AS avg_as_zero,
       AVG(COALESCE(score, 0)) AS avg_coalesced
FROM (VALUES ROW(4), ROW(5), ROW(NULL), ROW(3)) AS r(score);
Result
+---------+--------+-------+-------------------+-------------+---------------+
| reviews | scored | total | avg_skipping_null | avg_as_zero | avg_coalesced |
+---------+--------+-------+-------------------+-------------+---------------+
|       4 |      3 |    12 |            4.0000 |      3.0000 |        3.0000 |
+---------+--------+-------+-------------------+-------------+---------------+
1 row in set (0.000 sec)
What’s happening
  1. The derived table has 4 rows; one score is NULL. COUNT(*) counts all 4 rows; COUNT(score) counts only the 3 non-NULL scores.
  2. SUM(score) skips the NULL and adds 4 + 5 + 3 = 12. It does not become NULL just because one input is — unlike 4 + NULL, which is NULL.
  3. AVG(score) is 12 / 3 = 4.0000: the NULL row isn't part of the average at all.
  4. SUM(score) / COUNT(*) is 12 / 4 = 3.0000, and AVG(COALESCE(score, 0)) gives the same: those treat "no score" as a zero score.
  5. Which one is right is a business decision, not a SQL one. Just know that AVG silently picks "ignore the missing ones".
AdvancedPitfall: aggregates beside plain columns
AdvancedRelated: aggregates as window functions
Predict the result
SQL
SELECT SUM(price), COUNT(*), COALESCE(MAX(price), 0)
FROM products
WHERE price > 5000;
Show answer

One row: NULL, 0, 0.

What’s happening
  1. No product costs more than 5000 (the most expensive is the MacBook Air at 1299.00), so the WHERE lets zero rows through.
  2. There's no GROUP BY, so the query still returns exactly one row.
  3. SUM(price) over nothing is NULL; COUNT(*) is 0.
  4. MAX(price) is NULL too, and COALESCE replaces it with 0.
  5. If you added GROUP BY category_id, you'd get no rows at all: there would be zero groups, and grouping never invents an empty one.

#COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)

Added

COUNT comes in three flavours that answer three different questions, and interviewers love asking about the difference:

FormCountsSkips
COUNT(*)rowsnothing
COUNT(expr)rows where expr is not NULLNULLs
COUNT(DISTINCT expr)different non-NULL values of exprNULLs and repeats

Think of a class register. COUNT(*) is "how many students are on the list". COUNT(email) is "how many gave us an email address". COUNT(DISTINCT city) is "how many different cities do they come from".

SQL
SELECT COUNT(*)             AS customers,
       COUNT(city)          AS with_city,
       COUNT(DISTINCT city) AS cities
FROM customers;
Result
+-----------+-----------+--------+
| customers | with_city | cities |
+-----------+-----------+--------+
|         6 |         5 |      4 |
+-----------+-----------+--------+
1 row in set (0.000 sec)
What’s happening
  1. customers has 6 rows, so COUNT(*) is 6. It doesn't look at any column; it only counts rows.
  2. Sofia's city is NULL. COUNT(city) counts rows where city is not NULL: 6 - 1 = 5.
  3. The five cities are Toronto, Mumbai, London, Toronto, Dubai. COUNT(DISTINCT city) removes the repeat of Toronto and still ignores the NULL: 4.
  4. NULL is never counted as "a city" by either column form. If you wanted "unknown" to count as its own value, you'd write COUNT(DISTINCT COALESCE(city, '(none)')).
AdvancedCOUNT after a LEFT JOIN

The place this matters most is a LEFT JOIN. Count orders per customer, including customers who have never ordered:

SQL
SELECT c.name,
       COUNT(*)    AS count_star,
       COUNT(o.id) AS count_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
Result
+-------+------------+--------------+
| name  | count_star | count_orders |
+-------+------------+--------------+
| Rohit |          2 |            2 |
| Maya  |          2 |            2 |
| Liam  |          2 |            2 |
| Sofia |          1 |            1 |
| Noah  |          1 |            1 |
| Zara  |          1 |            0 |
+-------+------------+--------------+
6 rows in set (0.000 sec)
What’s happening
  1. The LEFT JOIN keeps every customer. Zara has no orders, so she appears once with every o. column set to NULL.
  2. GROUP BY c.id, c.name makes one group per customer. Rohit's group has 2 joined rows (orders 1 and 2), Maya's 2, Liam's 2, Sofia's 1, Noah's 1, Zara's 1 (the NULL-padded row).
  3. COUNT(*) counts joined rows, so it says Zara has 1 order — wrong.
  4. COUNT(o.id) counts only rows where o.id is not NULL. Zara's padded row has o.id = NULL, so she correctly gets 0.
  5. Rule of thumb: after an outer join, count a column from the optional side, ideally its primary key (which can only be NULL on padded rows).
AdvancedMyth: COUNT(1) is faster
AdvancedCounting distinct combinations

COUNT(DISTINCT …) can take several columns, counting distinct combinations (a MySQL extension; PostgreSQL needs COUNT(DISTINCT (a, b))):

SQL
SELECT COUNT(DISTINCT customer_id)         AS customers_who_ordered,
       COUNT(DISTINCT customer_id, status) AS customer_status_pairs,
       COUNT(DISTINCT status)              AS statuses_used
FROM orders;
Result
+-----------------------+-----------------------+---------------+
| customers_who_ordered | customer_status_pairs | statuses_used |
+-----------------------+-----------------------+---------------+
|                     5 |                     7 |             4 |
+-----------------------+-----------------------+---------------+
1 row in set (0.000 sec)
What’s happening
  1. The 8 orders belong to customers 1, 1, 2, 3, 3, 4, 5, 2. Distinct customers: 1, 2, 3, 4, 5 → 5 (Zara, customer 6, never ordered).
  2. Distinct (customer_id, status) pairs: (1, shipped), (1, paid), (2, shipped), (3, cancelled), (3, shipped), (4, pending), (5, paid) → 7. Maya's two orders are both shipped, so they collapse into one pair.
  3. COUNT(DISTINCT status) is 4: every status in the ENUM is used at least once.
  4. If any of the listed columns is NULL in a row, MySQL skips that row's combination entirely, the same way single-column COUNT(DISTINCT) skips NULL.
AdvancedPerformance: counting big InnoDB tables
Spot the bug

"Number of employees per department, including empty departments":

SQL
SELECT d.name, COUNT(*) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
Show answer

Research shows a headcount of 1 instead of 0.

SQL
SELECT d.name, COUNT(*) AS wrong, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
Result
+-------------+-------+-----------+
| name        | wrong | headcount |
+-------------+-------+-----------+
| Engineering |     4 |         4 |
| Research    |     1 |         0 |
| Sales       |     3 |         3 |
| Support     |     2 |         2 |
+-------------+-------+-----------+
4 rows in set (0.000 sec)
What’s happening
  1. Research has no employees, so the LEFT JOIN produces one row for it with e.id, e.name and so on all NULL.
  2. COUNT(*) counts that padded row → 1.
  3. COUNT(e.id) ignores it because e.id is NULL → 0. Engineering, Sales and Support are unaffected because every one of their joined rows has a real e.id.
  4. Also notice Jonas (no department) doesn't appear at all: he's on the optional side of this join, and only departments drive the rows.

#GROUP BY

Added

GROUP BY turns one funnel into many. MySQL sorts the rows into buckets — one bucket per distinct value of the grouping columns — and runs every aggregate separately for each bucket. The result has one row per group.

Picture a pile of receipts. GROUP BY department_id sorts them into labelled trays, one per department; then for every tray you count the receipts and add them up. The rows in the output are the trays, not the receipts — which is why every output column has to be either a tray label (a grouping column) or a summary of the tray (an aggregate).

SQL
SELECT department_id,
       COUNT(*)      AS headcount,
       SUM(salary)   AS payroll,
       AVG(salary)   AS avg_salary,
       MAX(hired_on) AS latest_hire
FROM employees
GROUP BY department_id
ORDER BY department_id;
Result
+---------------+-----------+-----------+---------------+-------------+
| department_id | headcount | payroll   | avg_salary    | latest_hire |
+---------------+-----------+-----------+---------------+-------------+
|          NULL |         1 |  91000.00 |  91000.000000 | 2025-06-16  |
|             1 |         4 | 587000.00 | 146750.000000 | 2021-05-03  |
|             2 |         3 | 324000.00 | 108000.000000 | 2023-01-09  |
|             3 |         2 | 160000.00 |  80000.000000 | 2024-04-01  |
+---------------+-----------+-----------+---------------+-------------+
4 rows in set (0.000 sec)
What’s happening
  1. FROM employees reads all 10 rows. There's no WHERE, so all of them go into grouping.
  2. GROUP BY department_id creates one bucket per distinct value: 1 (Asha, Ben, Chloe, Dev), 2 (Elena, Farid, Grace), 3 (Hiro, Isla) and NULL (Jonas). All NULLs go into one group, even though NULL = NULL isn't true in a WHERE — grouping treats NULLs as "not distinct" from each other.
  3. The aggregates run per bucket. Engineering: 185000 + 142000 + 142000 + 118000 = 587000, average 146750. Sales: 324000, average 108000. Support: 160000, average 80000. Jonas's group: one row, 91000.
  4. Research (department 4) has no employees, so it has no bucket and no row. GROUP BY only groups rows that exist; to list empty groups, start from departments and LEFT JOIN, as in the previous topic.
  5. ORDER BY department_id puts the NULL group first, because MySQL sorts NULL before every value in ascending order.
AdvancedMulti-column and expression grouping

Grouping by several columns and by expressions

Group by more than one column and you get one group per combination. You can also group by an expression or by a select-list alias (MySQL allows aliases in GROUP BY; the SQL standard doesn't):

SQL
SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
       o.status,
       COUNT(DISTINCT o.id)               AS orders,
       SUM(i.quantity * i.unit_price)     AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY month, o.status
ORDER BY month, o.status;
Result
+---------+-----------+--------+---------+
| month   | status    | orders | revenue |
+---------+-----------+--------+---------+
| 2025-02 | shipped   |      2 | 2005.00 |
| 2025-02 | cancelled |      1 | 1299.00 |
| 2025-03 | pending   |      1 |  249.00 |
| 2025-03 | paid      |      1 |   88.00 |
| 2025-03 | shipped   |      1 |  127.00 |
| 2025-04 | paid      |      1 | 1016.00 |
| 2025-04 | shipped   |      1 |  116.00 |
+---------+-----------+--------+---------+
7 rows in set (0.000 sec)
What’s happening
  1. The join produces one row per order line (13 rows): each line carries its order's date and status.
  2. DATE_FORMAT(…, '%Y-%m') turns 2025-02-01 10:00:00 into 2025-02, so all February orders share a month value. GROUP BY month, o.status then makes one group per (month, status) combination that actually occurs.
  3. February has three orders: 1 (shipped, 957.00), 3 (shipped, 1048.00) and 4 (cancelled, 1299.00). So (2025-02, shipped) gets 2 orders and 957 + 1048 = 2005.00, and (2025-02, cancelled) gets 1 order and 1299.00.
  4. COUNT(DISTINCT o.id) is needed because the join duplicated each order once per line; COUNT(*) would count lines, not orders.
  5. ORDER BY month, o.status sorts the ENUM column by its definition order (pending, paid, shipped, cancelled), not alphabetically — so within February shipped comes before cancelled.

#HAVING vs WHERE

Added

WHERE and HAVING both filter, but at different moments. WHERE filters rows before they are grouped; HAVING filters groups after they've been aggregated. That's why WHERE can't mention SUM(…) — the sums don't exist yet — and why HAVING usually does.

Back to the receipt trays: WHERE is the bouncer at the door who throws out individual receipts before they're sorted into trays. HAVING comes along afterwards and throws out whole trays whose totals don't qualify.

SQL
SELECT department_id,
       COUNT(*)    AS headcount,
       AVG(salary) AS avg_salary
FROM employees
WHERE hired_on >= '2019-01-01'
GROUP BY department_id
HAVING COUNT(*) >= 2
ORDER BY department_id;
Result
+---------------+-----------+---------------+
| department_id | headcount | avg_salary    |
+---------------+-----------+---------------+
|             1 |         3 | 134000.000000 |
|             2 |         3 | 108000.000000 |
|             3 |         2 |  80000.000000 |
+---------------+-----------+---------------+
3 rows in set (0.000 sec)
What’s happening
  1. WHERE hired_on >= '2019-01-01' runs first, row by row. It removes Asha (hired 2018-03-01) — only her — so 9 rows go on to grouping.
  2. GROUP BY department_id builds the groups from those 9 rows: Engineering is now Ben, Chloe and Dev (3 rows, average (142000 + 142000 + 118000) / 3 = 134000), Sales still 3, Support still 2, and Jonas alone in the NULL group.
  3. HAVING COUNT(*) >= 2 looks at each group's count and drops the NULL group (count 1).
  4. The three surviving groups are returned. Note that Engineering's average dropped from 146750 to 134000: removing Asha in WHERE changed what got averaged.
  5. That's the key difference: moving a condition between WHERE and HAVING doesn't just move it — it changes what's being filtered (rows vs groups).
AdvancedError: aggregates in WHERE

Trying to filter on an aggregate in WHERE is an error, because WHERE runs before any aggregate is computed:

SQL
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 100000
GROUP BY department_id;
Result
ERROR 1111 (HY000): Invalid use of group function
What’s happening
  1. MySQL evaluates WHERE once per input row. At that point there are no groups, so AVG(salary) has nothing to average over.
  2. MySQL reports it as ERROR 1111 (HY000): Invalid use of group function — "group function" is MySQL's old name for an aggregate.
  3. The fix is to move the condition to HAVING AVG(salary) > 100000, which runs after the groups' averages exist.
AdvancedRow filters belong in WHERE

Same answer, different cost

A condition on a grouping column can go in either place and give the same result. It should go in WHERE, so rows are discarded before the work of grouping:

SQL
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE department_id IN (1, 2)
GROUP BY department_id;

SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING department_id IN (1, 2);
Result
+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         4 |
|             2 |         3 |
+---------------+-----------+
2 rows in set (0.000 sec)

+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         4 |
|             2 |         3 |
+---------------+-----------+
2 rows in set (0.000 sec)
What’s happening
  1. Both return Engineering (4) and Sales (3).
  2. The first discards Support's and Jonas's rows before grouping, and can use an index on department_id to read only the wanted rows.
  3. The second groups all 10 rows into four groups, computes four counts, then throws two groups away. On 10 rows nobody cares; on 10 million, the wasted grouping is real.
  4. MySQL's optimizer doesn't always push HAVING conditions down into WHERE for you, so put row conditions in WHERE and keep HAVING for conditions on aggregates.
Predict the result
SQL
SELECT department_id, COUNT(*) AS n
FROM employees
WHERE salary > 100000
GROUP BY department_id
HAVING n > 1;
Show answer

One row: department 1 with n = 4.

SQL
SELECT department_id, COUNT(*) AS n
FROM employees
WHERE salary > 100000
GROUP BY department_id
HAVING n > 1;
Result
+---------------+---+
| department_id | n |
+---------------+---+
|             1 | 4 |
+---------------+---+
1 row in set (0.000 sec)
What’s happening
  1. WHERE salary > 100000 keeps Asha, Ben, Chloe, Dev (department 1) and Elena (department 2). Everyone else earns 99000 or less.
  2. Grouping gives department 1 → 4 rows, department 2 → 1 row.
  3. HAVING n > 1 (MySQL allows the alias) drops department 2.
  4. Swap the filters — WHERE on count is impossible, but HAVING AVG(salary) > 100000 with no WHERE would return departments 1 and 2 (averages 146750 and 108000), a completely different question.

#ONLY_FULL_GROUP_BY

Added

After grouping, each output row stands for a whole group of input rows. A column in the SELECT list is only meaningful if it has one value per group: a grouping column, an aggregate, or a column that's determined by the grouping columns (a functional dependency, like a department's name once you've grouped by its primary key id). Anything else is ambiguous: "which of the four names in this group do you want?"

ONLY_FULL_GROUP_BY is the SQL mode that makes MySQL reject ambiguous queries instead of guessing. It has been on by default since MySQL 5.7.5, when MySQL also learned to detect functional dependencies so that valid queries stopped being rejected. Before that (and on any server where someone turned the mode off, which is still common after upgrades), MySQL silently picked any value from the group.

SQL
SELECT department_id, name, MIN(salary) AS lowest
FROM employees
GROUP BY department_id;
Result
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ch04.employees.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
What’s happening
  1. GROUP BY department_id makes four groups. department_id is fine (it's the grouping column) and MIN(salary) is fine (an aggregate).
  2. name is neither. The Engineering group has four names — Asha, Ben, Chloe and Dev — and nothing tells MySQL which one belongs next to MIN(salary).
  3. name is also not functionally dependent on department_id: knowing the department doesn't tell you the name.
  4. So MySQL rejects the query with ERROR 1055 (42000), naming the offending expression and column (ch04.employees.name, because this site's examples run in a database called ch04).
  5. The person who wrote it almost certainly wanted "the lowest-paid person in each department". That's a top-N-per-group problem, solved properly with a window function or a join back to the group's minimum — not with GROUP BY alone.
AdvancedMode off: arbitrary values per group

What happens with the mode off

Here's the same query with ONLY_FULL_GROUP_BY removed from the session's sql_mode — what MySQL 5.6 did by default, and what any server with a customised sql_mode may still do:

SQL
SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

SELECT department_id, name, MIN(salary) AS lowest
FROM employees
GROUP BY department_id;
Result
Query OK, 0 rows affected (0.000 sec)

+---------------+-------+-----------+
| department_id | name  | lowest    |
+---------------+-------+-----------+
|          NULL | Jonas |  91000.00 |
|             1 | Asha  | 118000.00 |
|             2 | Elena |  95000.00 |
|             3 | Hiro  |  72000.00 |
+---------------+-------+-----------+
4 rows in set (0.000 sec)
What’s happening
  1. sys.list_drop() removes one item from the comma-separated sql_mode list, leaving the rest (strict mode and so on) in place. It only affects this session.
  2. Now the query runs. MIN(salary) is correct: 118000.00 for Engineering (Dev's salary).
  3. But name shows Asha for Engineering — the highest earner, not the lowest. MySQL took name from whatever row it met first in the group, unrelated to the row that supplied MIN(salary).
  4. Sales shows Elena next to Farid's 95000.00, Support shows Hiro next to Isla's 72000.00. Every "answer" is wrong, and nothing warned you.
  5. The manual calls these values nondeterministic: a different plan, index or MySQL version can return a different name, and adding ORDER BY doesn't control which one you get. This is why upgrades to 5.7 broke so many apps — the mode exposed queries that had always been subtly wrong.
AdvancedFunctional dependencies and ANY_VALUE

Functional dependencies: when MySQL knows it's safe

MySQL 5.7.5+ accepts a non-grouped column when it can prove it's determined by the grouping columns — most usefully, columns of a table whose primary key (or a UNIQUE NOT NULL column) you grouped by:

SQL
SELECT d.id, d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id;
Result
+----+-------------+-----------+
| id | name        | headcount |
+----+-------------+-----------+
|  1 | Engineering |         4 |
|  2 | Sales       |         3 |
|  3 | Support     |         2 |
|  4 | Research    |         0 |
+----+-------------+-----------+
4 rows in set (0.000 sec)
What’s happening
  1. GROUP BY d.id groups by the primary key of departments.
  2. d.name isn't in GROUP BY, but each d.id has exactly one name, so every group has exactly one d.name. MySQL detects this functional dependency and accepts the query.
  3. COUNT(e.id) gives 4, 3, 2 and 0 for Research (the padded row's e.id is NULL).
  4. If you grouped by e.department_id instead and selected d.name, MySQL would also accept it when the join condition d.id = e.department_id makes the dependency provable. When it can't prove one, it errors — and then you add the column to GROUP BY or use ANY_VALUE().

ANY_VALUE(col) is the explicit way to say "I know any value will do" (for example, a column you know is constant within each group but MySQL can't prove it):

SQL
SELECT department_id,
       COUNT(*)                    AS headcount,
       ANY_VALUE(name)             AS some_member
FROM employees
GROUP BY department_id;
Result
+---------------+-----------+-------------+
| department_id | headcount | some_member |
+---------------+-----------+-------------+
|          NULL |         1 | Jonas       |
|             1 |         4 | Asha        |
|             2 |         3 | Elena       |
|             3 |         2 | Hiro        |
+---------------+-----------+-------------+
4 rows in set (0.000 sec)
What’s happening
  1. ANY_VALUE(name) switches off the ONLY_FULL_GROUP_BY check for that one expression; it returns a value from one row of the group.
  2. The output shows Jonas, Asha, Elena and Hiro — the same arbitrary picks as with the mode off — but now the query documents that the choice is arbitrary.
  3. Use it only when the choice really doesn't matter or you know all the values in the group are the same. If you need a specific row (the lowest-paid person), ANY_VALUE doesn't help.

#WITH ROLLUP

Added

Reports often want subtotals and a grand total under the detail rows: revenue per city, then per country, then overall. You could run three queries and UNION them, but GROUP BY … WITH ROLLUP does it in one pass: after the normal groups, it adds super-aggregate rows that summarise "everything at this level", with the rolled-up columns shown as NULL.

Think of a spreadsheet with subtotal rows. GROUP BY a, b WITH ROLLUP produces the (a, b) groups, then a subtotal row for each a (with b shown as NULL), then one grand total row (with both NULL). It rolls up right to left, so the order of columns in GROUP BY matters.

SQL
SELECT c.city,
       o.status,
       SUM(i.quantity * i.unit_price) AS revenue
FROM customers c
JOIN orders o      ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY c.city, o.status WITH ROLLUP;
Result
+---------+---------+---------+
| city    | status  | revenue |
+---------+---------+---------+
| London  | shipped |  127.00 |
| London  | NULL    |  127.00 |
| Mumbai  | shipped | 1164.00 |
| Mumbai  | NULL    | 1164.00 |
| Toronto | paid    | 1104.00 |
| Toronto | shipped |  957.00 |
| Toronto | NULL    | 2061.00 |
| NULL    | NULL    | 3352.00 |
+---------+---------+---------+
8 rows in set (0.000 sec)
What’s happening
  1. The joins and WHERE keep the order lines of paid and shipped orders (orders 1, 2, 3, 5, 7, 8). Liam's cancelled order and Sofia's pending one are gone — which also means no NULL-city rows (Sofia) are left.
  2. The normal groups are (city, status) pairs: London/shipped 127.00 (order 5), Mumbai/shipped 1048 + 116 = 1164.00, Toronto/paid 88 + 1016 = 1104.00 (Rohit's order 2 and Noah's order 7), Toronto/shipped 957.00.
  3. After each city's rows, WITH ROLLUP adds a row with status = NULL: that city's total over all statuses. Toronto's is 1104 + 957 = 2061.00.
  4. The final row has both columns NULL: the grand total, 127 + 1164 + 2061 = 3352.00.
  5. There is no "per status across all cities" subtotal: ROLLUP only removes columns from the right. That combination would need CUBE — see below.
AdvancedGROUPING() for labels and sorting

Telling a subtotal NULL from a real NULL: GROUPING()

If a grouping column can contain NULL in the data, its NULL group and the rollup's subtotal row look identical. Jonas has no department, so:

SQL
SELECT department_id,
       SUM(salary)            AS payroll,
       GROUPING(department_id) AS is_total
FROM employees
GROUP BY department_id WITH ROLLUP;
Result
+---------------+------------+----------+
| department_id | payroll    | is_total |
+---------------+------------+----------+
|          NULL |   91000.00 |        0 |
|             1 |  587000.00 |        0 |
|             2 |  324000.00 |        0 |
|             3 |  160000.00 |        0 |
|          NULL | 1162000.00 |        1 |
+---------------+------------+----------+
5 rows in set (0.000 sec)
What’s happening
  1. The first row is the real NULL group: Jonas, 91000.00. Its GROUPING(department_id) is 0 because the NULL came from the data.
  2. Rows for departments 1, 2 and 3 follow with their payrolls.
  3. The last row also shows department_id = NULL, but it's the grand total 1162000.00, and GROUPING(department_id) is 1: this NULL was produced by the rollup.
  4. GROUPING(col) (added in MySQL 8.0.1) returns 1 exactly when col was rolled up in that row. That's the only reliable way to tell the two kinds of NULL apart.

Use GROUPING() to label the rows, and (since MySQL 8.0.12) to sort the totals where you want them:

SQL
SELECT IF(GROUPING(d.name), 'All departments', COALESCE(d.name, '(no department)')) AS department,
       COUNT(*)    AS headcount,
       SUM(e.salary) AS payroll
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
GROUP BY d.name WITH ROLLUP
ORDER BY GROUPING(d.name), payroll DESC;
Result
+-----------------+-----------+------------+
| department      | headcount | payroll    |
+-----------------+-----------+------------+
| Engineering     |         4 |  587000.00 |
| Sales           |         3 |  324000.00 |
| Support         |         2 |  160000.00 |
| (no department) |         1 |   91000.00 |
| All departments |        10 | 1162000.00 |
+-----------------+-----------+------------+
5 rows in set (0.000 sec)
What’s happening
  1. The LEFT JOIN keeps Jonas with d.name = NULL; grouping by d.name gives Engineering, Sales, Support and a NULL group.
  2. IF(GROUPING(d.name), 'All departments', …) labels the super-aggregate row; COALESCE(d.name, '(no department)') labels Jonas's real NULL group. The two NULLs now read differently.
  3. ORDER BY GROUPING(d.name), payroll DESC sorts the detail rows (GROUPING = 0) first, by payroll — Engineering 587000, Sales 324000, Support 160000, then Jonas 91000 — and puts the total (GROUPING = 1) last.
  4. In MySQL 5.7 and 8.0 before 8.0.12, ORDER BY and WITH ROLLUP couldn't be combined at all; you had to wrap the rollup query in a derived table and sort outside it.
AdvancedROLLUP(), CUBE and GROUPING SETS

ROLLUP(...) syntax, CUBE and GROUPING SETS

MySQL 9.7 also accepts the standard spelling GROUP BY ROLLUP (a, b) (documented in the 8.4 and 9.7 manuals) as an alternative to WITH ROLLUP. The other standard grouping extensions, CUBE and GROUPING SETS, are parsed but don't run on ordinary InnoDB tables:

SQL
SELECT status, COUNT(*) AS orders FROM orders GROUP BY ROLLUP (status);
SELECT status, COUNT(*) FROM orders GROUP BY CUBE (status);
Result
+-----------+--------+
| status    | orders |
+-----------+--------+
| pending   |      1 |
| paid      |      2 |
| shipped   |      4 |
| cancelled |      1 |
| NULL      |      8 |
+-----------+--------+
5 rows in set (0.000 sec)

ERROR 3889 (HY000): Secondary engine operation failed. Reason: "You have not defined the secondary engine for at least one of the query tables [ch04.orders].".
What’s happening
  1. GROUP BY ROLLUP (status) gives one row per status plus the grand total row (8 orders), exactly like GROUP BY status WITH ROLLUP. The statuses come out in ENUM definition order because rollup groups in sorted order.
  2. GROUP BY CUBE (status) fails with ERROR 3889 (HY000), complaining that no "secondary engine" is defined: in MySQL 9.7 these extensions are only executed by a secondary engine (the HeatWave analytics engine in Oracle's cloud), which this sandbox doesn't have.
  3. On plain MySQL, emulate CUBE/GROUPING SETS with several GROUP BY queries combined with UNION ALL. PostgreSQL supports all three natively.

PostgreSQL 18 has the full set — here, totals by city, by status, and overall, in one query:

PostgreSQL 18
SELECT c.city, o.status, SUM(i.quantity * i.unit_price) AS revenue
FROM customers c
JOIN orders o      ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY GROUPING SETS ((c.city), (o.status), ())
ORDER BY GROUPING(c.city), c.city, o.status;
Result
  city   | status  | revenue 
---------+---------+---------
 London  |         |  127.00
 Mumbai  |         | 1164.00
 Toronto |         | 2061.00
         | paid    | 1104.00
         | shipped | 2248.00
         |         | 3352.00
(6 rows)
What’s happening
  1. GROUPING SETS ((c.city), (o.status), ()) asks for three separate groupings in one pass: per city, per status, and the empty grouping () — the grand total.
  2. Per city: London 127.00, Mumbai 1164.00, Toronto 2061.00 — the same subtotals the MySQL rollup produced.
  3. Per status: paid 1104.00, shipped 2248.00 — the "across all cities" subtotal ROLLUP couldn't give.
  4. The final row is the grand total 3352.00. In MySQL you'd write this as three GROUP BY queries glued with UNION ALL.

#GROUP_CONCAT and JSON aggregation

Added

Sometimes the summary you want isn't a number but the list itself: the names in each department, the tags on each post, the items of each order as JSON for an API. GROUP_CONCAT glues a group's values into one string; JSON_ARRAYAGG and JSON_OBJECTAGG build a JSON array or object.

The mental model is a string builder (or array builder) that runs once per group: every row in the group appends its value, and the finished string comes out as the group's value. It's the SQL equivalent of rows.map(r => r.name).join(', ').

SQL
SELECT d.name AS department,
       GROUP_CONCAT(e.name ORDER BY e.salary DESC, e.name SEPARATOR ', ') AS team,
       GROUP_CONCAT(DISTINCT YEAR(e.hired_on) ORDER BY YEAR(e.hired_on)) AS hire_years
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY d.id;
Result
+-------------+-----------------------+---------------------+
| department  | team                  | hire_years          |
+-------------+-----------------------+---------------------+
| Engineering | Asha, Ben, Chloe, Dev | 2018,2019,2020,2021 |
| Sales       | Elena, Grace, Farid   | 2019,2022,2023      |
| Support     | Hiro, Isla            | 2020,2024           |
| Research    | NULL                  | NULL                |
+-------------+-----------------------+---------------------+
4 rows in set (0.000 sec)
What’s happening
  1. One group per department. Inside each, GROUP_CONCAT(e.name ORDER BY e.salary DESC, e.name SEPARATOR ', ') sorts the group's rows by salary (highest first), breaks the Ben/Chloe tie by name, and joins the names with ', '.
  2. Engineering becomes Asha, Ben, Chloe, Dev. Without the e.name tie-breaker the order of Ben and Chloe (both 142000.00) would be up to the engine.
  3. GROUP_CONCAT(DISTINCT YEAR(e.hired_on) …) removes duplicate years before joining, using the default separator ,: Engineering's hires were in 2018, 2019, 2020 and 2021.
  4. Research has only a padded NULL row. GROUP_CONCAT skips NULLs, and with nothing left it returns NULL (not an empty string).
AdvancedPitfall: GROUP_CONCAT truncation
AdvancedBuilding JSON with aggregates

JSON_ARRAYAGG and JSON_OBJECTAGG

For anything a program will parse, build JSON instead of a delimited string — values containing commas or quotes can't break it. Here each of Rohit's orders gets its items as a JSON array of objects, ready to send from an API:

SQL
SELECT o.id AS order_id,
       o.status,
       JSON_ARRAYAGG(JSON_OBJECT('product', p.name, 'qty', i.quantity, 'price', i.unit_price)) AS items
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p    ON p.id = i.product_id
WHERE o.customer_id = 1
GROUP BY o.id, o.status;
Result
+----------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| order_id | status  | items                                                                                                                                                |
+----------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | shipped | [{"qty": 1, "price": 899.00, "product": "ThinkBook 14"}, {"qty": 2, "price": 29.00, "product": "USB-C Charger"}]                                     |
|        2 | paid    | [{"qty": 1, "price": 39.00, "product": "SQL Performance Explained"}, {"qty": 1, "price": 49.00, "product": "Designing Data-Intensive Applications"}] |
+----------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
What’s happening
  1. The joins produce one row per order line of customer 1: two lines for order 1, two for order 2.
  2. For each line, JSON_OBJECT('product', p.name, 'qty', i.quantity, 'price', i.unit_price) builds one JSON object. Keys come out sorted by MySQL's JSON storage format (by key length, then bytes), which is why qty and price print before product — JSON objects are unordered, so that's legal.
  3. JSON_ARRAYAGG(…) collects the group's objects into one array, giving items for each order.
  4. The DECIMAL price keeps its exact value (899.00) in the JSON. A driver like mysql2 returns the column as a string you JSON.parse.
  5. This one query replaces "fetch orders, then fetch items for each order" — the N+1 pattern from ORMs.

JSON_OBJECTAGG(key, value) turns a two-column result into one object, handy for lookup maps:

SQL
SELECT JSON_OBJECTAGG(status, n) AS orders_by_status
FROM (SELECT status, COUNT(*) AS n FROM orders GROUP BY status) AS t;
Result
+---------------------------------------------------------+
| orders_by_status                                        |
+---------------------------------------------------------+
| {"paid": 2, "pending": 1, "shipped": 4, "cancelled": 1} |
+---------------------------------------------------------+
1 row in set (0.000 sec)
What’s happening
  1. The derived table counts orders per status: shipped 4, paid 2, pending 1, cancelled 1.
  2. JSON_OBJECTAGG(status, n) makes each row one key/value pair of a single object.
  3. If two rows have the same key, the last one read wins (('a', 1), ('a', 2) gives {"a": 2}), so make keys unique. A NULL key is an error: ERROR 3158 (22032): JSON documents may not contain NULL member names.

#Conditional aggregation and pivots

Added

Conditional aggregation means putting a CASE (or a condition) inside an aggregate, so each aggregate only counts or sums the rows that match. One pass over the table can then produce many differently filtered numbers side by side — which is exactly what a pivot is: turning values that live in rows (status = 'paid', status = 'shipped') into columns.

Think of a tally sheet with several columns. Every row walks past all of them, and each column only makes a mark if the row matches its condition. SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) is the "paid" column of that sheet.

SQL
SELECT c.name,
       COUNT(o.id)                                           AS orders,
       SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN o.status = 'paid'    THEN 1 ELSE 0 END) AS paid,
       COUNT(CASE WHEN o.status IN ('pending', 'cancelled') THEN 1 END) AS other
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
Result
+-------+--------+---------+------+-------+
| name  | orders | shipped | paid | other |
+-------+--------+---------+------+-------+
| Rohit |      2 |       1 |    1 |     0 |
| Maya  |      2 |       2 |    0 |     0 |
| Liam  |      2 |       1 |    0 |     1 |
| Sofia |      1 |       0 |    0 |     1 |
| Noah  |      1 |       0 |    1 |     0 |
| Zara  |      0 |       0 |    0 |     0 |
+-------+--------+---------+------+-------+
6 rows in set (0.000 sec)
What’s happening
  1. The LEFT JOIN gives one row per order (plus a padded row for Zara), and GROUP BY makes one group per customer.
  2. For each row, CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END is 1 or 0, and SUM adds them up. Maya's two orders (3 and 8) are both shipped → 2.
  3. The COUNT(CASE … THEN 1 END) form relies on the missing ELSE producing NULL, which COUNT skips. Liam's cancelled order and Sofia's pending one are the only others.
  4. Zara's padded row has o.status = NULL, so every CASE gives 0 (or NULL); her row is all zeros — but note SUM gives 0 here only because of the ELSE 0. Without it, SUM over only NULLs would be NULL.
  5. One scan, five numbers per customer. Doing the same with five separate WHERE queries would read the table five times and then need joining back together.
AdvancedBoolean sums and PostgreSQL FILTER

MySQL's boolean shortcut

In MySQL a comparison returns 1 or 0 (or NULL), so SUM(o.status = 'paid') counts matches directly. Here's a revenue pivot by month — rows are months, columns are statuses:

SQL
SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
       SUM(IF(o.status = 'shipped', i.quantity * i.unit_price, 0)) AS shipped,
       SUM(IF(o.status = 'paid',    i.quantity * i.unit_price, 0)) AS paid,
       SUM(i.quantity * i.unit_price)                              AS all_statuses,
       SUM(o.status = 'cancelled')                                 AS cancelled_lines
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY month
ORDER BY month;
Result
+---------+---------+---------+--------------+-----------------+
| month   | shipped | paid    | all_statuses | cancelled_lines |
+---------+---------+---------+--------------+-----------------+
| 2025-02 | 2005.00 |    0.00 |      3304.00 |               1 |
| 2025-03 |  127.00 |   88.00 |       464.00 |               0 |
| 2025-04 |  116.00 | 1016.00 |      1132.00 |               0 |
+---------+---------+---------+--------------+-----------------+
3 rows in set (0.000 sec)
What’s happening
  1. Each joined row is one order line with its order's month and status.
  2. SUM(IF(o.status = 'shipped', i.quantity * i.unit_price, 0)) adds the line's value only when the order shipped. February: orders 1 and 3 shipped → 957 + 1048 = 2005.00.
  3. all_statuses sums every line, so February's 3304.00 also includes Liam's cancelled 1299.00.
  4. SUM(o.status = 'cancelled') adds 1 for each line where the comparison is true: February has one cancelled line.
  5. IF() and boolean sums are MySQL-specific. SUM(CASE WHEN … THEN … ELSE 0 END) is the portable spelling that works everywhere.

PostgreSQL has a standard, more readable syntax for this: the FILTER clause.

PostgreSQL 18
SELECT to_char(o.ordered_at, 'YYYY-MM') AS month,
       SUM(i.quantity * i.unit_price) FILTER (WHERE o.status = 'shipped') AS shipped,
       SUM(i.quantity * i.unit_price) FILTER (WHERE o.status = 'paid')    AS paid,
       COUNT(*) FILTER (WHERE o.status = 'cancelled')                     AS cancelled_lines
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY month
ORDER BY month;
Result
  month  | shipped |  paid   | cancelled_lines 
---------+---------+---------+-----------------
 2025-02 | 2005.00 |         |               1
 2025-03 |  127.00 |   88.00 |               0
 2025-04 |  116.00 | 1016.00 |               0
(3 rows)
What’s happening
  1. aggregate(...) FILTER (WHERE condition) feeds only matching rows to that one aggregate, like a private WHERE.
  2. February matches MySQL: shipped 2005.00, one cancelled line.
  3. Where no row matches, a filtered SUM is the sum of an empty set, so it's NULL: February has no paid orders and its paid cell is blank (psql prints NULL as nothing). The filtered COUNT gives 0 for March and April. MySQL's version showed 0.00 only because of the IF(…, …, 0).
  4. MySQL 9.7 doesn't support FILTER; use SUM(CASE …) there.
AdvancedDynamic pivot with a prepared statement

Dynamic pivots

SQL needs the column list at parse time, so a pivot whose columns depend on the data (one column per category, whatever categories exist) has to be built as a string and run as a prepared statement. GROUP_CONCAT builds the column list:

SQL
SET @cols = (
  SELECT GROUP_CONCAT(
           CONCAT('SUM(IF(c.name = ', QUOTE(name), ', i.quantity * i.unit_price, 0)) AS `', name, '`')
           ORDER BY name)
  FROM categories
  WHERE parent_id IS NOT NULL OR id = 4
);
SET @sql = CONCAT(
  'SELECT DATE_FORMAT(o.ordered_at, ''%Y-%m'') AS month, ', @cols,
  ' FROM orders o JOIN order_items i ON i.order_id = o.id',
  ' JOIN products p ON p.id = i.product_id JOIN categories c ON c.id = p.category_id',
  ' WHERE o.status <> ''cancelled'' GROUP BY month ORDER BY month');
PREPARE pivot FROM @sql;
EXECUTE pivot;
DEALLOCATE PREPARE pivot;
Result
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)
Statement prepared

+---------+-------------+--------+---------+--------+
| month   | Accessories | Books  | Laptops | Phones |
+---------+-------------+--------+---------+--------+
| 2025-02 |      307.00 |   0.00 |  899.00 | 799.00 |
| 2025-03 |      278.00 | 186.00 |    0.00 |   0.00 |
| 2025-04 |      116.00 | 117.00 |  899.00 |   0.00 |
+---------+-------------+--------+---------+--------+
3 rows in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)
What’s happening
  1. The first SET reads the categories products are attached to (the three under Electronics, plus Books) and builds one SUM(IF(c.name = 'Books', …, 0)) AS `Books` expression per category, comma-separated by GROUP_CONCAT.
  2. QUOTE(name) wraps each name as a safely escaped SQL string literal, so a category called Kids' Books couldn't break the generated SQL.
  3. The second SET glues the fixed parts of the query around @cols. Doubled single quotes (''%Y-%m'') are how you write a quote inside a SQL string literal.
  4. PREPARE parses the generated text, EXECUTE runs it, DEALLOCATE frees it. Add a category tomorrow and the pivot grows a column without anyone editing the query.
  5. February, for example: Laptops 899.00 (order 1), Phones 799.00 (order 3), Accessories 58 + 249 = 307.00 — the cancelled MacBook order is excluded by the WHERE.
Predict the result
SQL
SELECT SUM(CASE WHEN salary > 100000 THEN 1 END) AS high,
       SUM(CASE WHEN salary > 500000 THEN 1 END) AS very_high,
       COUNT(CASE WHEN salary > 500000 THEN 1 END) AS very_high_count
FROM employees;
Show answer

high = 5, very_high = NULL, very_high_count = 0.

SQL
SELECT SUM(CASE WHEN salary > 100000 THEN 1 END) AS high,
       SUM(CASE WHEN salary > 500000 THEN 1 END) AS very_high,
       COUNT(CASE WHEN salary > 500000 THEN 1 END) AS very_high_count
FROM employees;
Result
+------+-----------+-----------------+
| high | very_high | very_high_count |
+------+-----------+-----------------+
|    5 |      NULL |               0 |
+------+-----------+-----------------+
1 row in set (0.000 sec)
What’s happening
  1. With no ELSE, each CASE returns 1 for matching rows and NULL for the rest.
  2. Five employees earn more than 100000 (Asha, Ben, Chloe, Dev, Elena), so high sums five 1s → 5.
  3. Nobody earns more than 500000, so very_high sums ten NULLs. SUM ignores them, has nothing left, and returns NULL.
  4. COUNT of ten NULLs is 0. That's why SUM(CASE … ELSE 0 END) or COUNT(CASE … END) is the safer habit for counting.

SQL and MySQL written fresh; MongoDB built from Rohit’s “mongoDB interview” doc. Every query was run on MySQL 9.7, MongoDB 9.0, Redis 8.10 or PostgreSQL 18.

RohitDownloads · All chapters

Sync progress across devices

Type the same private phrase on your Mac and your phone, and your Learned ticks follow you between them — on every study site.

The phrase never leaves this device: only a fingerprint of it is sent, and the server stores a fingerprint of that. Anyone who knows the phrase could see or change your ticks, so pick something you don’t use elsewhere.

Sync is on in this browser.

Sync ID

This ID must be the same on every device. If another device shows a different one, its phrase is different (capital letters count): tap “Turn off here” on it and type the phrase again exactly.