CUBE operator is used to calculate subtotals and grand total for all combinations of grouping columns specified in the GROUP BY clause. CUBE uses the same functionality of ROLLUP Operator but it returns the subtotal and grand total for all combinations of grouping columns specified in the GROUP BY clause.
Syntax of group by cube
Select columns, Aggregate function(column)
from table (where filter condition- optional ) group by cube(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 cube: this is used to get the sub total and grand total of all combination.
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.
See the example
select empid, |
See the result.
No comments:
Post a Comment
If you have any doubt, please let me know.