Cube Operator in SQL Server 2008
In this article I describe about cube operator in SQL Server.
Introduction
In this article I describe about cube operator in SQL Server 2008.
Cube Operator
Cube operator is used with Group By clause to return every possible combination of group or subgroup in the result. It presents the data in aggregate form.
First of all we create a table named emp
Creation of table:
create table Stocks
(
Apparel varchar(15),
Store varchar(15),
Number int
)
Insertion of data:
insert into Stocks
select 'Shirt', 'Bindals' ,25 union all
select 'Trousers','Bindals', 30 union all
select 'Jeans', 'Labhjis' ,15 union all
select 'Shirt', 'Labhjis', 25 union all
select 'Jeans', 'Labhjis', 34
A table emp has the following records:
select * from Stocks
Output:
Using Cube Operator:
Now if we want to quick look at various apparels stock, type following code to see all possible combinations of Apparel and Store:
select Apparel,Store, SUM(Number) AS Quantity
FROM Stocks
GROUP BY Apparel, Store WITH CUBE
Output: