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) |
Ø 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, |
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, or we can write same query as below select empid, |
“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.
No comments:
Post a Comment
If you have any doubt, please let me know.