Chapter 05 · Advanced SQL: Aggregation, Window Functions, JSON, and Analytical Patterns
GROUP BY, HAVING, Functional Dependence, ROLLUP, and Aggregation Design
Design reliable MySQL aggregates from an explicit row grain, with correct WHERE/HAVING semantics, ONLY_FULL_GROUP_BY functional-dependence checks, NULL handling, and ROLLUP subtotals.
Learning outcomes
A manager asks for “one row per region showing closed work, open work, labor, and parts cost, plus subtotals.” That request sounds like a few aggregate functions, but reliable aggregation begins before SUM() or COUNT(): you must define the grain of one output row. If the grain is unclear, joins can multiply facts, nonaggregated columns can become nondeterministic, and subtotal NULL values can be mistaken for ordinary missing data.
This lesson builds aggregation from the row-grain contract outward. It reconnects the portable SQL ideas from the prerequisite course, then focuses on MySQL behavior under ONLY_FULL_GROUP_BY, functional-dependence detection, and ROLLUP/GROUPING().
Define the grain of an aggregate result before writing GROUP BY and verify it with controlled row counts.
Distinguish WHERE from HAVING and aggregate expressions from nonaggregate expressions.
Explain how ONLY_FULL_GROUP_BY and MySQL functional-dependence detection protect deterministic grouped results.
Use ROLLUP and GROUPING() to produce and label subtotal/grand-total rows without confusing them with ordinary NULLs.
Build and verify a regional ServiceHub operational summary with production-minded correctness checks.
Mandatory labs use MySQL Community Server 8.4 LTS, with the current downloadable 8.4.10 build as the reproducible reference. Capture SELECT VERSION() and @@SESSION.sql_mode before comparing results because grouping behavior depends on sql_mode.
Create the Chapter 05 analytics laboratory
The chapter extends the ServiceHub domain from Chapter 04. Six customers belong to four regions; twelve work orders carry status, priority, labor minutes, exact DECIMAL parts cost, and optional JSON attributes. A small service-day table is included for the window-function lesson. The dataset stays intentionally small so you can calculate expected answers by hand before asking MySQL.
DROP DATABASE IF EXISTS servicehub_analytics_lab;CREATE DATABASE servicehub_analytics_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_analytics_lab;CREATE TABLE customers ( customer_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, customer_name VARCHAR(100) NOT NULL, region VARCHAR(20) NOT NULL, signup_date DATE NOT NULL) ENGINE=InnoDB;CREATE TABLE technicians ( technician_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, technician_name VARCHAR(100) NOT NULL, team_name VARCHAR(30) NOT NULL) ENGINE=InnoDB;CREATE TABLE work_orders ( work_order_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, customer_id BIGINT UNSIGNED NOT NULL, technician_id BIGINT UNSIGNED NULL, status VARCHAR(20) NOT NULL, priority TINYINT UNSIGNED NOT NULL, opened_at DATETIME NOT NULL, closed_at DATETIME NULL, labor_minutes INT UNSIGNED NOT NULL, parts_cost DECIMAL(10,2) NOT NULL DEFAULT 0.00, summary VARCHAR(180) NOT NULL, attributes JSON NULL, CONSTRAINT chk_priority CHECK (priority BETWEEN 1 AND 3), CONSTRAINT chk_attributes_object CHECK ( attributes IS NULL OR JSON_TYPE(attributes) = 'OBJECT' ), CONSTRAINT fk_analytics_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id), CONSTRAINT fk_analytics_technician FOREIGN KEY (technician_id) REFERENCES technicians(technician_id)) ENGINE=InnoDB;CREATE TABLE service_days ( customer_id BIGINT UNSIGNED NOT NULL, service_date DATE NOT NULL, PRIMARY KEY (customer_id, service_date), CONSTRAINT fk_service_days_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)) ENGINE=InnoDB;INSERT INTO customers VALUES (1,'Northwind Clinic','north','2026-01-05'), (2,'Contoso Foods','south','2026-01-20'), (3,'Alpine School','north','2026-02-03'), (4,'Fabrikam Lab','east','2026-02-14'), (5,'Tailspin Hotel','west','2026-03-01'), (6,'Adventure Works','south','2026-03-21');INSERT INTO technicians VALUES (10,'Mina','operations'), (11,'Arman','electrical'), (12,'Sara','mechanical'), (13,'Laleh','controls');INSERT INTO work_orders VALUES (1001,1,11,'closed',1,'2026-01-10 08:00:00','2026-01-10 10:00:00', 90,120.50,'Sterilizer temperature alarm', JSON_OBJECT('channel','portal','asset',JSON_OBJECT('type','sterilizer','critical',TRUE),'skills',JSON_ARRAY('electrical','controls'),'sla_hours',4,'note',NULL)), (1002,1,12,'closed',2,'2026-01-20 09:30:00','2026-01-21 12:15:00',160, 75.00,'Pump seal replacement', JSON_OBJECT('channel','phone','asset',JSON_OBJECT('type','pump','critical',FALSE),'skills',JSON_ARRAY('mechanical'),'sla_hours',24)), (1003,1,11,'open',1,'2026-02-05 08:30:00',NULL,45,0.00,'Sterilizer controller diagnostic', JSON_OBJECT('channel','portal','asset',JSON_OBJECT('type','sterilizer','critical',TRUE),'skills',JSON_ARRAY('electrical','controls'),'sla_hours',4)), (1004,2,13,'closed',2,'2026-02-06 10:00:00','2026-02-06 11:30:00',60,25.00,'Conveyor sensor alignment', JSON_OBJECT('channel','api','asset',JSON_OBJECT('type','conveyor','critical',FALSE),'skills',JSON_ARRAY('controls'),'sla_hours',8)), (1005,2,12,'closed',2,'2026-02-07 10:00:00','2026-02-07 12:00:00',60,25.00,'Packaging pump inspection', JSON_OBJECT('channel','phone','asset',JSON_OBJECT('type','pump','critical',FALSE),'skills',JSON_ARRAY('mechanical'),'sla_hours',8)), (1006,3,11,'closed',3,'2026-03-01 07:30:00','2026-03-02 13:00:00',200,300.00,'Boiler pressure sensor replacement', JSON_OBJECT('channel','email','asset',JSON_OBJECT('type','boiler','critical',TRUE),'skills',JSON_ARRAY('electrical'),'sla_hours',12)), (1007,3,13,'open',2,'2026-03-02 08:00:00',NULL,120,80.00,'HVAC controller tuning', JSON_OBJECT('channel','portal','asset',JSON_OBJECT('type','hvac','critical',TRUE),'skills',JSON_ARRAY('controls'),'sla_hours',8)), (1008,4,13,'closed',1,'2026-03-03 08:00:00','2026-03-03 09:00:00',60,15.00,'Microscope stage calibration', JSON_OBJECT('channel','portal','asset',JSON_OBJECT('type','microscope','critical',FALSE),'skills',JSON_ARRAY('calibration','controls'),'sla_hours',4)), (1009,4,11,'closed',2,'2026-03-03 08:00:00','2026-03-03 10:00:00',60,15.00,'Lab sensor cable replacement', JSON_OBJECT('channel','email','asset',JSON_OBJECT('type','sensor','critical',FALSE),'skills',JSON_ARRAY('electrical'),'sla_hours',8)), (1010,5,12,'open',3,'2026-04-01 09:00:00',NULL,30,0.00,'Guest elevator pump noise',NULL), (1011,6,13,'closed',1,'2026-04-02 08:00:00','2026-04-02 09:15:00',75,50.00,'Line controller firmware check', JSON_OBJECT('channel','api','asset',JSON_OBJECT('type','controller','critical',TRUE),'skills',JSON_ARRAY('controls'),'sla_hours',4)), (1012,6,11,'closed',1,'2026-04-03 08:00:00','2026-04-04 10:00:00',75,50.00,'Motor sensor fault tracing', JSON_OBJECT('channel','api','asset',JSON_OBJECT('type','motor','critical',TRUE),'skills',JSON_ARRAY('electrical'),'sla_hours',4));INSERT INTO service_days VALUES (1,'2026-01-10'),(1,'2026-01-11'),(1,'2026-01-12'),(1,'2026-01-20'),(1,'2026-01-21'), (2,'2026-02-06'),(2,'2026-02-08'),(2,'2026-02-09'), (3,'2026-03-01'),(3,'2026-03-02'),(3,'2026-03-03'),(3,'2026-03-10');SELECT VERSION() AS server_version, DATABASE() AS current_schema, @@SESSION.sql_mode AS session_sql_mode;SELECT COUNT(*) AS customers FROM customers;SELECT COUNT(*) AS technicians FROM technicians;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT COUNT(*) AS service_days FROM service_days;Expected seed counts are 6 customers, 4 technicians, 12 work orders, and 12 service-day rows. The work-order table contains 9 closed rows and 3 open rows. Treat these counts as test fixtures: if they differ, repair the seed state before debugging an aggregate.
Start with grain: what does one output row mean?
Suppose the report grain is one row per region. Then every selected expression must either identify that region or summarize rows inside that region. If you later add status to GROUP BY, the grain changes to one row per region and status. That is a different report, not a formatting tweak.
| Requested report | Grain | Typical grouping key |
|---|---|---|
| Regional workload | one row per region | region |
| Regional workload by state | one row per region + status | region, status |
| Technician daily labor | one row per technician + calendar day | technician_id, DATE(opened_at) |
| Grand total | one row for the entire qualifying set | no GROUP BY |
SELECT c.region, COUNT(*) AS work_order_count, SUM(w.labor_minutes) AS labor_minutes, SUM(w.parts_cost) AS parts_costFROM work_orders AS wJOIN customers AS c ON c.customer_id = w.customer_idGROUP BY c.regionORDER BY c.region;The expected counts by region are east 2, north 5, south 4, and west 1. Before trusting labor or money totals, verify the counts. Counts are often the fastest way to detect accidental join multiplication.
WHERE filters rows before grouping; HAVING filters groups
WHERE answers “which input rows are eligible?” HAVING answers “which groups survive after aggregation?” Use the earliest semantically correct filter. If you need only closed work orders, put status='closed' in WHERE; do not group all rows and then simulate that row filter indirectly.
SELECT c.region, COUNT(*) AS closed_orders, SUM(w.parts_cost) AS closed_parts_costFROM work_orders AS wJOIN customers AS c ON c.customer_id = w.customer_idWHERE w.status = 'closed'GROUP BY c.regionHAVING COUNT(*) >= 2ORDER BY c.region;-- east: 2, north: 3, south: 4; west is absentThe WHERE clause removes open work orders before aggregation. HAVING COUNT(*) >= 2 then removes any region whose remaining group has fewer than two closed orders. Putting an aggregate such as COUNT(*) in WHERE is conceptually wrong because the group does not exist at that stage.
ONLY_FULL_GROUP_BY: make nondeterminism visible
With ONLY_FULL_GROUP_BY enabled—which is part of the default MySQL 8.4 SQL mode—MySQL rejects a grouped query when a selected nonaggregate expression is neither grouped nor functionally dependent on the grouping columns. This is a correctness guard, not an inconvenience to disable reflexively.
SELECT @@SESSION.sql_mode;-- Wrong: a customer can have work orders with different statuses.SELECT customer_id, status, COUNT(*) AS order_countFROM work_ordersGROUP BY customer_id;-- Expected with ONLY_FULL_GROUP_BY: error 1055 (expression not in GROUP BY-- and not functionally dependent on grouped columns).If you disabled ONLY_FULL_GROUP_BY, MySQL could choose an arbitrary status value from each customer group. Adding ORDER BY does not make that choice deterministic because ordering happens after the grouped value has already been chosen.
Do not remove ONLY_FULL_GROUP_BY merely to make an ambiguous report execute. Repair the grain: group by status if status is part of the row identity, aggregate it if a summary is intended, or remove it if it does not belong in the result.
Functional dependence: when a non-grouped column is still deterministic
Functional dependence means one value uniquely determines another. A primary key determines every other column in its row. MySQL can detect many such relationships and accept a nonaggregated expression when the grouping key guarantees exactly one value for it.
SELECT c.customer_id, c.customer_name, COUNT(w.work_order_id) AS order_countFROM customers AS cLEFT JOIN work_orders AS w ON w.customer_id = c.customer_idGROUP BY c.customer_idORDER BY c.customer_id;customer_name is not listed in GROUP BY, but customers.customer_id is a primary key, so each grouped customer ID determines exactly one customer name. That is different from the earlier status example, where one customer can have both open and closed orders.
ANY_VALUE() exists for cases where you intentionally accept an arbitrary representative or where a real dependence is outside what MySQL can infer. It is not a substitute for understanding grain.
NULL and aggregate functions: know what is counted
COUNT(*) counts rows. COUNT(expression) counts non-NULL expression values. Most numeric aggregates ignore NULL inputs. This matters with closed_at: open work orders have no close timestamp, so COUNT(closed_at) counts closed rows in this particular schema, whereas COUNT(*) counts all rows.
SELECT COUNT(*) AS all_orders, COUNT(closed_at) AS rows_with_close_time, COUNT(DISTINCT customer_id) AS customers_with_ordersFROM work_orders;-- Expected: 12, 9, 6Do not rely on a nullable column as a proxy for business state unless the schema contract guarantees that relationship. Here it is pedagogically useful, but the authoritative state is still the status column and its business rules.
ROLLUP and GROUPING(): distinguish real NULL from subtotal NULL
WITH ROLLUP adds super-aggregate rows. For grouping keys, MySQL represents those higher-level rows with NULL. That creates an ambiguity if the underlying data can also contain ordinary NULL. GROUPING() resolves it: it returns 1 when the expression is a super-aggregate placeholder and 0 for an ordinary grouped value.
SELECT CASE WHEN GROUPING(c.region) = 1 THEN 'ALL REGIONS' ELSE c.region END AS region, CASE WHEN GROUPING(w.status) = 1 THEN 'ALL STATUSES' ELSE w.status END AS status, COUNT(*) AS order_count, SUM(w.labor_minutes) AS labor_minutes, SUM(w.parts_cost) AS parts_costFROM work_orders AS wJOIN customers AS c ON c.customer_id = w.customer_idGROUP BY c.region, w.status WITH ROLLUPORDER BY GROUPING(c.region), c.region, GROUPING(w.status), w.status;The rows labeled ALL STATUSES are regional subtotals; the final ALL REGIONS / ALL STATUSES row is the grand total. Use GROUPING() rather than testing region IS NULL if real data can contain a NULL region.
Hands-on lab: build an operational aggregation contract
- Recreate the seed and verify the four fixture counts.
- Write one row per region with order count, closed count, open count, labor minutes, and parts cost.
- Move a
status='closed'condition betweenWHEREand conditional aggregation; explain why the resulting grain/meaning changes. - Trigger the
ONLY_FULL_GROUP_BYerror by selectingstatuswhile grouping only by customer. - Repair the query by choosing a correct grain, not by disabling the mode.
- Add
WITH ROLLUPand label subtotal rows withGROUPING().
SELECT c.region, COUNT(*) AS all_orders, SUM(CASE WHEN w.status='closed' THEN 1 ELSE 0 END) AS closed_orders, SUM(CASE WHEN w.status='open' THEN 1 ELSE 0 END) AS open_orders, SUM(w.labor_minutes) AS labor_minutes, SUM(w.parts_cost) AS parts_costFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idGROUP BY c.regionORDER BY c.region;Knowledge check
- What is the grain of SELECT region, status, COUNT(*) ... GROUP BY region, status?
- Why should a row-level status filter normally be in WHERE rather than HAVING?
- What problem does ONLY_FULL_GROUP_BY prevent?
- Why can customer_name be selected when grouping by the customers primary key?
- How does GROUPING(region) differ from region IS NULL in a ROLLUP result?
Reveal answers
- One output row per distinct region-and-status pair.
- WHERE removes ineligible input rows before grouping; HAVING is for conditions on the resulting groups.
- It prevents nondeterministic selection of nonaggregated values that are neither grouped nor functionally dependent on the grouping key.
- A primary key uniquely determines the other columns in its row, and MySQL can detect that functional dependence.
- GROUPING(region)=1 specifically identifies a super-aggregate placeholder introduced by ROLLUP; region IS NULL cannot distinguish that from an ordinary NULL grouped value.
Production judgment and next bridge
Aggregation belongs close to the data when it reduces rows cleanly and the query remains operationally affordable. But a correct aggregate can still be expensive: broad scans, large sorts, many groups, and join multiplication can consume CPU, memory, and temporary-table I/O. Watch query latency, rows examined, temporary-table behavior, and plan changes; Chapter 09 will teach systematic plan engineering.
Next: grouped aggregates collapse rows. Window functions let you calculate rankings, running values, previous/next comparisons, and per-group top-N while preserving individual rows.