Multiple columns
SELECT column1, column2, aggregate_function(column)
FROM table
GROUP BY ROLLUP (column1, column2);
Applying Rollup in the above code for multiple columns.
SELECT COALESCE(Department ,’ Total’)as Department ,gender, SUM(Salary) as Salary from Employee1 Group By ROLLUP(Department , Gender)
Output:
Multiple Column
How it Works
Multiple Rollup min
Rollup in SQL Server
The ROLLUP operator enhances the capabilities of the GROUP BY clause by enabling the computation of subtotals and grand totals for a set of columns. It produces a result set that incorporates rows at various levels of aggregation. ROLLUP streamlines the aggregation process by eliminating the need for separate queries to obtain subtotals and totals, resulting in a more streamlined and efficient approach. It is a powerful extension of the GROUP BY clause, enabling users to generate summary reports effortlessly.
Syntax:
SELECT column, aggregate_function(column)
FROM table
GROUP BY ROLLUP (column);
In this syntax:
- ‘column’ represents the column by which you wish to group your data.
- ‘aggregate_function(column)’ is an optional step, allowing you to specify an aggregation function (e.g., SUM, COUNT, AVG) for performing calculations on the grouped data.
- ‘table’ designates the table being queried.
- ‘GROUP BY’ is a clause used to specify a single column or multiple columns to create a group on which the aggregate operation is performed.
- ‘ROLLUP’ is used with the combination of the GROUP BY clause for creating multiple groups (i.e., grouping set) and hierarchically applies the aggregate function.