Single Column
SELECT column, aggregate_function(column)
FROM table
GROUP BY ROLLUP (column);
Applying Rollup in the above code
select COALESCE(Department, ‘Total’) as Department SUM(Salary) from Employee1 Group By ROLLUP (Department)
Output:
NOTE:Here COALESCE is used because this function gives value NULL if not given a name so COALESCE gives the name ‘Total’.
How it Works
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.