ROLLUP for subtotals in MySQL

Introduction

ROLLUP helps you quickly get totals and subtotals in one query, saving time and effort.

When you want to see sales totals by product and also a grand total.

When you need subtotals for different categories in a report.

When you want to summarize data at multiple levels without writing many queries.

When preparing financial reports that show breakdowns and overall totals.

When analyzing data grouped by multiple columns and you want quick summaries.

Syntax

MySQL

SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
GROUP BY column1, column2 WITH ROLLUP;

ROLLUP adds extra rows with subtotals and a grand total at the end.

The order of columns in GROUP BY matters for how subtotals are calculated.

Examples

Shows sales totals by employee and department, plus subtotals for each department and a grand total.

MySQL

SELECT department, employee, SUM(sales) AS total_sales
FROM sales_data
GROUP BY department, employee WITH ROLLUP;

Gives total expenses per category and a grand total at the end.

MySQL

SELECT category, SUM(amount) AS total_amount
FROM expenses
GROUP BY category WITH ROLLUP;

Sample Program

This query groups sales by region and product, then adds subtotals for each region and a grand total for all sales.

MySQL

CREATE TABLE sales (
  region VARCHAR(20),
  product VARCHAR(20),
  amount INT
);

INSERT INTO sales VALUES
('North', 'Apples', 100),
('North', 'Oranges', 150),
('South', 'Apples', 200),
('South', 'Oranges', 100);

SELECT region, product, SUM(amount) AS total_amount
FROM sales
GROUP BY region, product WITH ROLLUP;

Important Notes

NULL in the result means a subtotal or grand total row.

Use ORDER BY carefully if you want to keep subtotal rows grouped properly.

Summary

ROLLUP adds subtotal and total rows automatically in grouped queries.

It helps summarize data at multiple levels in one query.

NULL values in output mark subtotal or total rows.