Aggregations, ROLLUP, GROUPING Patterns, Set Operations, and Reporting Workflows

Learning outcomes

The chapter ends with a reporting problem that combines almost everything learned so far. ServiceHub needs regional totals, technician subtotals, a grand total, a list of regions present in two different work queues, and a report that does not accidentally merge NULL business data with ROLLUP-generated subtotal markers. Reporting SQL is often where teams tolerate ambiguous grouping, implicit conversion and client-side patching because “it is only a report.” In production, those shortcuts can create financially or operationally wrong numbers.

This lesson treats a report as a data contract. GROUP BY defines result grain, WHERE filters before grouping, HAVING filters groups, and WITH ROLLUP creates super-aggregate rows. MariaDB supports UNION/UNION ALL and, since 10.3, INTERSECT and EXCEPT; current versions support ALL/DISTINCT variants. Set operators combine result sets by position and compatible types/collations, not by column name. Final ordering and subtotal labeling therefore need explicit design.

1. GROUP BY defines the report grain

If the business question is “total labor cost per region,” the output grain is one row per region. Every selected nonaggregate expression should be functionally appropriate to that grain. MariaDB documentation notes that selecting non-grouped, nonaggregated columns can produce undefined row values when ONLY_FULL_GROUP_BY is not enforcing stricter semantics. A reliable report should not depend on whichever representative row the server happens to pick.

SELECT region,       COUNT(*) AS work_orders,       SUM(labor_cost) AS total_cost,       AVG(labor_cost) AS avg_costFROM work_ordersGROUP BY regionORDER BY region;

The result has exactly one row for each distinct region according to the column collation. If case or accent differences must create separate business regions, that is a schema/collation decision—not something GROUP BY can infer for you.

2. WHERE and HAVING filter at different stages

WHERE filters input rows before grouping. HAVING filters the resulting groups after aggregates have been computed. Use WHERE for row-level eligibility because it reduces the data entering aggregation; use HAVING for conditions such as “only regions with at least three orders” or “technicians whose total cost exceeds 200.”

SELECT region,       COUNT(*) AS open_orders,       SUM(labor_cost) AS open_costFROM work_ordersWHERE status='open'GROUP BY regionHAVING SUM(labor_cost) >= 100ORDER BY region;

Placing a row predicate in HAVING can be legal in some forms but obscures the data flow and can prevent early filtering opportunities. Write the stage that matches the requirement.

3. WITH ROLLUP creates super-aggregate rows

MariaDB WITH ROLLUP adds subtotal and grand-total rows to grouped results. The grouping column that is rolled up is represented as NULL in the super-aggregate row. With multiple grouping columns, rollup produces a hierarchy of subtotals. MariaDB documentation also states that WITH ROLLUP cannot be combined directly with ordinary ORDER BY; grouping-column ASC/DESC affects some ordering while rollup rows remain at the end of their grouping level.

SELECT region,       status,       COUNT(*) AS work_orders,       SUM(labor_cost) AS total_costFROM work_ordersGROUP BY region, status WITH ROLLUP;

Do not immediately label every NULL region as “Grand Total” with COALESCE unless the underlying grouping column is declared NOT NULL or you otherwise distinguish genuine NULL business values. In this lab region and status are NOT NULL, so the rollup NULL markers are unambiguous. In a nullable production dimension, use a deliberate reporting model or an outer query that preserves the distinction rather than collapsing real missing data into subtotal labels.

4. UNION ALL versus UNION: bag or set semantics

UNION ALL concatenates results and preserves duplicates. UNION applies duplicate elimination. Choose from the report semantics before performance: if the same row legitimately appears in two sources and must be counted twice, UNION would silently change meaning. If the report asks for the distinct combined set, UNION states that requirement directly.

SELECT region FROM work_orders WHERE status='open'UNION ALLSELECT region FROM work_orders WHERE status='closed';SELECT region FROM work_orders WHERE status='open'UNIONSELECT region FROM work_orders WHERE status='closed';

The number of rows differs because the first query preserves every source row while the second returns distinct region values across both branches. Do not use UNION as a reflexive “dedupe fix” for a join that produced unintended duplicates; repair the join grain first.

5. INTERSECT and EXCEPT express set relationships directly

MariaDB supports INTERSECT to keep rows present in both result sets and EXCEPT to keep rows from the left result that are absent from the right. They can often make a reporting intention clearer than a join or NOT EXISTS when the input is naturally two projected sets. On current MariaDB, INTERSECT has higher precedence than UNION and EXCEPT unless Oracle mode changes precedence rules, so parentheses are wise when a mixed expression could be misread.

SELECT region FROM work_orders WHERE status='open'INTERSECTSELECT region FROM work_orders WHERE status='closed';

SELECT region FROM work_orders WHERE status='open'EXCEPTSELECT region FROM work_orders WHERE status='closed';

By default these operators use distinct set semantics. Current MariaDB also supports ALL variants that preserve multiplicities according to the operator’s rules. Use ALL only when duplicate multiplicity is part of the requirement; otherwise it makes a report harder to interpret.

6. Set-operation branches align by position, type and collation

Set operators combine the first expression of one SELECT with the first expression of the next, the second with the second, and so on. Alias names do not realign columns. The branches must produce compatible types, and character expressions carry collation implications. A migration that changes a branch from a numeric ID to text can introduce implicit conversion or client-metadata changes even if the query still executes.

SELECT CAST(work_order_id AS CHAR(30)) AS item_key,       region AS label,       'open_work_order' AS source_typeFROM work_ordersWHERE status='open'UNION ALLSELECT CAST(technician_id AS CHAR(30)) AS item_key,       display_name AS label,       'technician' AS source_typeFROM techniciansORDER BY source_type, item_key;

The explicit casts communicate the intended common representation. In a real integration, prefer keeping identifiers typed in a normalized interface rather than stringifying everything merely to make a set operator compile. The report contract should specify output types as well as values.

7. Stable report ordering belongs at the outer level

A final report needs one explicit presentation order. For a set operation, apply ORDER BY to the combined result rather than assuming each branch’s internal access order will survive composition. Likewise, rollup output may need an outer query or application-layer ordering/labeling if the product requires a custom subtotal presentation that the native WITH ROLLUP ordering rules do not express cleanly.

SELECT *FROM (  SELECT region, status, COUNT(*) AS work_orders, SUM(labor_cost) AS total_cost  FROM work_orders  GROUP BY region, status) AS report_rowsORDER BY region, status;

This simple wrapper illustrates a boundary principle: produce semantically correct rows first, then apply presentation rules. For complex pivot tables, localized labels, charts or interactive drill-down, SQL may be best used to produce a stable normalized dataset while the reporting tool handles visual layout.

8. Deliberately wrong report: subtotal labeling without proving NULL meaning

A common shortcut is COALESCE(region,'TOTAL') in a rollup query. It appears to work when the sample has no NULL regions. If production later permits NULL region as “unassigned,” the report merges real unassigned data with the grand-total marker. The repair is not a more clever string function; it is to preserve enough state to distinguish business NULL from rollup-generated NULL, or to enforce a NOT NULL dimension when that reflects the domain.

9. Evidence-driven report lab

EXPLAIN FORMAT=JSONSELECT region, status, COUNT(*) AS work_orders, SUM(labor_cost) AS total_costFROM work_ordersGROUP BY region, status WITH ROLLUP;ANALYZE FORMAT=JSONSELECT region, status, COUNT(*) AS work_orders, SUM(labor_cost) AS total_costFROM work_ordersGROUP BY region, status WITH ROLLUP;

On the tiny lab, runtime numbers are not performance claims. In a production report, measure data volume, group cardinality, temporary-table/sort behavior, memory, concurrency and whether a covering/index-assisted strategy is actually chosen. A report that runs once per day can tolerate a very different cost profile from a dashboard query executed hundreds of times per minute.

1. Produce region totals and verify one row per region.
2. Add WHERE and HAVING filters and explain which stage removes which data.
3. Generate region/status rollups and identify detail, subtotal and grand-total rows.
4. Compare UNION ALL with UNION using open/closed region projections.
5. Use INTERSECT and EXCEPT to answer overlap/difference questions.
6. Wrap a set/report query and apply one deterministic outer ORDER BY.
7. Inspect output column types/aliases from each branch before combining heterogeneous sources.
8. Run EXPLAIN/ANALYZE and record local execution evidence without publishing it as a universal benchmark.

10. Chapter 05 summary and bridge

Chapter 05 connected advanced SQL semantics to observable MariaDB behavior. You made filtering NULL-aware, ordering deterministic, pagination explicit, joins cardinality-driven, outer-join predicates safe, existence queries semijoin-aware, recursive CTEs bounded, windows frame-aware, and reports explicit about grouping and set semantics. The consistent method was concept → query → observation → explanation → failure → repair → verification.

Chapter 06 moves from reading data to changing it under concurrency. The same precision becomes even more important: INSERT/UPDATE/DELETE variants, autocommit, transactions, isolation, row and metadata locks, deadlocks and retry policies determine whether concurrent ServiceHub actions remain correct when multiple sessions modify the same data.