SQL Reference - ROLLUP

Example

SELECT Country, State, COUNT(Name)
FROM Customers
GROUP BY Country, State WITH ROLLUP;

Output

| Country  | State   |  COUNT(Name)  |
|----------|---------|---------------|
| USA      | TX      | 100           |
| USA      | CA      | 120           |
| USA      | NULL    | 220           |
| Canada   | BC      | 150           |
| Canada   | NULL    | 150           |
| NULL     | NULL    | 370           |

Explanation

In the example, the ROLLUP operator is used to produce a result set that is similar to the one generated by the GROUP BY clause. However, with ROLLUP, extra rows are added to calculate the subtotals, which are grouped by Country and State. Also, a grand total row is added to the result set.

Example

SELECT region, dept, SUM(salary) as total_salary
FROM employee
GROUP BY ROLLUP(region, dept);

Output

region	dept	total_salary
East	HR	50000
East	IT	80000
East	NULL	130000
West	HR	45000
West	IT	75000
West	NULL	120000
NULL	NULL	250000

Explanation

In the provided sample, ROLLUP operator is used to create subtotals and grand totals in the result set. It generates a result set that is similar to the one generated by the GROUP BY clause, but with additional rows that represent subtotals and grand totals. Here, region and dept are the grouping columns. The ROLLUP operator creates a sub-total for each unique region and a grand total row at the end.

Example

SELECT Region, State, SUM(Sales) as TotalSales
FROM Sales
GROUP BY ROLLUP(Region, State);

Output

Region     | State    | TotalSales
-----------|----------|------------
West       | California | 25000
West       | Nevada   | 10000
West       | NULL     | 35000
NULL       | NULL     | 35000

Explanation

The ROLLUP operator is used to generate a result set that is similar to the one generated by the GROUP BY clause, but with additional rows that represent subtotals and grand totals. The NULL values in the result set represent subtotals and grand totals. In this specific example, the ROLLUP operator generates a total sales for each State under each Region, a subtotal sales for each Region (NULL in State column), and a grand total sales (NULL in both Region and State columns).

Example

SELECT location, department, SUM(sales) as total_sales
FROM sales_table
GROUP BY ROLLUP(location, department);

Output

LOCATION    | DEPARTMENT  | TOTAL_SALES
----------------------------------------
Los Angeles | Electronics | 100000
Los Angeles | Clothing    | 200000
Los Angeles | NULL        | 300000
New York    | Electronics | 150000
New York    | Clothing    | 250000
New York    | NULL        | 400000
NULL        | NULL        | 700000

Explanation

The ROLLUP operator is used to create subtotals and grand totals in the output of a SQL query. In this example, ROLLUP creates subtotals for each ‘location’ (i.e., total sales for all departments at each location) and a grand total for all locations and departments. A NULL value in ‘department’ represents the total sales for a location across all departments. A NULL value in both ‘location’ and ‘department’ represents the grand total sales for all locations and departments.