MySQL | Totals with WITH ROLLUP

When you use GROUP BY, MySQL calculates totals for each column listed after GROUP BY.
One limitation is that you may also want the grand total to appear together with the subtotal for each item.

In that case, use WITH ROLLUP.

WITH ROLLUP is used to calculate totals for each group at once.

WITH ROLLUP Syntax

SELECT "field1", SUM("field2")
FROM "table_name"
GROUP BY "field1"
WITH ROLLUP

WITH ROLLUP Examples

store_information Table

Assume the table above exists, and run the following statement.

SQL Example 1

SELECT store_name, SUM(sales)
FROM store_information
GROUP BY store_name
WITH ROLLUP

Result

SQL Example 2

SELECT region_name, store_name, SUM(sales)
FROM store_information
GROUP BY region_name, store_name
WITH ROLLUP

Result

The second row displays the total for East, 700, and the fifth row displays the total for West, 2050.
The final row displays the grand total, 2750.