ROLLUP for subtotals in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROLLUP operator do in a SQL query?

ROLLUP adds extra rows to the result set that show subtotals and a grand total for grouped data.

intermediate

How does ROLLUP affect the GROUP BY clause?

ROLLUP extends GROUP BY by creating grouping sets that include all levels of aggregation from detailed groups up to a grand total.

beginner

In MySQL, what value appears in the ROLLUP subtotal rows for the grouped columns?

The grouped columns in subtotal rows show NULL to indicate aggregation over all values in that column.

beginner

Write a simple SQL query using ROLLUP to get sales totals by region and product.

SELECT region, product, SUM(sales) AS total_sales FROM sales_table GROUP BY region, product WITH ROLLUP;

beginner

Why is ROLLUP useful in reports?

ROLLUP helps create reports that show detailed data plus subtotals and grand totals in one query, saving extra calculations.

What does the ROLLUP operator add to a GROUP BY query?

A Only detailed rows

B Only grand total row

C Subtotals and grand totals

D No additional rows

ROLLUP adds subtotal rows for each grouping level and a grand total row.

In a ROLLUP result, what does a NULL value in a grouped column mean?

A Missing data

B Error in query

C No rows matched

D Subtotal or total for that column

NULL indicates the subtotal or total row for that grouped column.

Which SQL clause is extended by ROLLUP?

A GROUP BY

B WHERE

C ORDER BY

D HAVING

ROLLUP extends the GROUP BY clause to add subtotals and totals.

What keyword is used in MySQL to enable ROLLUP?

A WITH ROLLUP

B ROLLUP ON

C GROUP BY ROLLUP

D ROLLUP ENABLE

MySQL uses WITH ROLLUP after GROUP BY to enable rollup subtotals.

If you GROUP BY region, product WITH ROLLUP, which row is NOT included?

A Subtotal by region

B Subtotal by product

C Grand total

D Detailed rows by region and product

ROLLUP creates subtotals by region and grand total, but not subtotal by product alone.

Explain how the ROLLUP operator works in SQL and why it is useful for generating subtotals and totals.

Think about how ROLLUP adds extra rows to grouped results.

Write a SQL query using ROLLUP to show sales totals by two categories and explain the output rows you expect.

Use GROUP BY with two columns and WITH ROLLUP.