ROLLUP in MySQL

Introduction
Business reports need subtotals and grand totals without complex UNION queries.
ROLLUP in MySQL automatically generates summary rows when used with GROUP BY.
It is widely used for:

  • Hierarchical summaries (city→department→total)
  • Financial reporting
  • Dashboard aggregations

What is ROLLUP
ROLLUP is a GROUP BY modifier that adds subtotal and grand total rows.
It creates multiple grouping levels from right to left in GROUP BY columns.

Basic Syntax

sql

SELECT column1, column2, AGGREGATE_FUNCTION(column) 

FROM table_name 

GROUP BY column1, column2 WITH ROLLUP; 

Example

sql

SELECT city, COUNT(*) as student_count 

FROM Students 

GROUP BY city WITH ROLLUP; 

Shows count per city + total row with NULL city.

ROLLUP Multiple Levels
Creates all subtotal combinations.

sql

SELECT city, department, AVG(marks) as avg_marks 

FROM Students 

GROUP BY city, department WITH ROLLUP; 

Results:

  • Individual city+department rows
  • City subtotals (department=NULL)
  • total (city+department=NULL)

ROLLUP Result Pattern
For GROUP BY col1, col2 WITH ROLLUP:

  1. col1, col2 (detail rows)
  2. col1, NULL (col1 subtotals)
  3. NULL, NULL (total)

ROLLUP with ORDER BY
Control display order.

sql

SELECT city, department, COUNT(*) as count 

FROM Students 

GROUP BY city, department WITH ROLLUP 

ORDER BY city, department; 

ROLLUP with WHERE Clause
Filter before rollup calculation.

sql

SELECT city, COUNT(*) as high_scorers 

FROM Students 

WHERE marks > 80 

GROUP BY city WITH ROLLUP; 

ROLLUP with Multiple Aggregates
Multiple calculations per level.

sql

SELECT city, 

       COUNT(*) as total_students, 

       AVG(marks) as avg_marks, 

       SUM(marks) as total_marks 

FROM Students 

GROUP BY city WITH ROLLUP; 

ROLLUP with JOIN
Summary across joined tables.

sql

SELECT c.course_name, COUNT(s.id) as enrollment 

FROM Courses c 

JOIN Students s ON c.id = s.course_id 

GROUP BY c.course_name WITH ROLLUP; 

Identifying ROLLUP Rows
NULL values indicate summary levels.

sql

SELECT

    IF(city IS NULL, 'GRAND TOTAL', city) as city_display,

    COUNT(*) as student_count 

FROM Students 

GROUP BY city WITH ROLLUP; 

ROLLUP vs GROUPING SETS
ROLLUP is shorthand for specific sets:
GROUP BY col1, col2 WITH ROLLUP =
GROUP BY (col1,col2), (col1), ()

Execution Order with ROLLUP
ROLLUP processes after:
FROM → WHERE → GROUP BY (with ROLLUP) → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

  • ROLLUP multiplies result rows (N+1 levels)
  • Index GROUP BY columns left-to-right
  • Use WHERE to reduce rows before ROLLUP
  • Large datasets: consider application-level totals

Important Notes

  • NULL in rightmost GROUP BY column = subtotal
  • All NULLs = grand total
  • Works only with GROUP BY
  • MySQL 8.0+ optimised ROLLUP performance
  • Combine with COALESCE for readable NULLs

Example Scenario
Sales report by region→product→total:

sql

SELECT

    COALESCE(region, 'ALL REGIONS') as region,

    COALESCE(product, 'ALL PRODUCTS') as product,

    COUNT(*) as orders,

    SUM(amount) as total_sales

FROM Orders

GROUP BY region, product WITH ROLLUP

ORDER BY region NULLS LAST, product NULLS LAST;

Common Mistakes

  • ROLLUP without GROUP BY (syntax error)
  • Forgetting indexes on GROUP BY columns
  • Misinterpreting NULL summary rows
  • Large tables without WHERE filtering
  • ORDER BY disrupting the ROLLUP hierarchy

Key Points to Remember

  • ROLLUP adds automatic subtotals/grand totals
  • Right-to-left grouping levels (col2→col1→total)
  • NULL values mark summary rows
  • Essential for hierarchical reporting
  • Index GROUP BY columns for performance
  • Use COALESCE/IF for readable NULL displays