GROUP BY ROLLUP clause in SQL Server

Only group by cannot aggregate data in multiple level of a hierarchy like displaying the subtotal or grand total. The group by rollup clause enhance the capabilities of the group by our data and aggregate into subtotal and grand total.

Syntax of group by rollup

Select columns, Aggregate function(column) from table (where filter condition- optional ) group by rollup(Columns)

     Ø  Column: list of column which we want to group by and show in the result set.

Ø  Aggregate function: it may be sum, average, count etc.

Ø  Table : table name

Ø  Where clause : if we need to filet data we will use where clause

Ø  Group by rollup: this is used to get the sub total and grand total.

See the example.

We have a table.

create table employee( id int identity(1,1) primary key, empid int, emp_name varchar(50), salary_withdraw decimal(18,10), withdraw_dt    date )

Inserting some records in this table.

insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1002,'01-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1000,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1020,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1050,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1030,'05-31-2024');   insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2002,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2009,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2000,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',1999,'05-31-2024');   insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1012,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1052,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1082,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1002,'05-31-2024');   insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',87,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',77,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',97,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',67,'05-31-2024');

See the records in the table. 

Now we want to get the total and average salary of the employee at employ level and finally we want to get the grand total. We want the result set as below.

To get this result we need to use rollup clause.

select empid,        emp_name,        Sum(salary_withdraw) as Salary,        withdraw_dt from   employee group  by rollup( empid, emp_name, withdraw_dt );

               

Oooo we are getting the null value in place of subtotal and grad total. To replace null value to subtotal and grand total we will use COALESCE. See below.

select empid,        case          when empid is not null then coalesce(emp_name, 'Sub Total')          else 'Grand Total'        end                  as emp_name,        Sum(salary_withdraw) as Salary,        withdraw_dt from   employee group  by rollup( empid, emp_name, withdraw_dt )    or we can write same query as below select empid,        case          when empid is not null then coalesce(emp_name, 'Sub Total')          else 'Grand Total'        end                  as emp_name,        Sum(salary_withdraw) as Salary,        withdraw_dt from   employee group  by empid,           emp_name,           withdraw_dt with rollup

 

 “GROUP BY” is suitable for basic grouping and summarization, “GROUP BY ROLLUP” offers enhanced functionality by providing subtotals and a grand total. Depending on our analytical needs, we can choose the appropriate statement to gain insights from our data effectively.