Cube clause in SQL Server

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,        emp_name,        Sum(salary_withdraw) as Salary from   employee group  by cube( empid, emp_name )

See the result.