Thursday, October 29, 2015

Cube and Rollup Functions in the SQL

CREATE TABLE [dbo].[Employees1]
(
[Id] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Dept] [nvarchar](50) NULL,
[Gender] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Salary] [float] NULL,
)

Insert into Employees Values(1,'John','IT','Male','UK',5000)
Insert into Employees Values(2,'Mary','HR','Female','India',3000)
Insert into Employees Values(3,'Todd','IT','Male','UK',3500)
Insert into Employees Values(4,'Pam','HR','Female','India',4000)
Insert into Employees Values(5,'Tatcher','Payroll','Male','USA',2000)
Insert into Employees Values(6,'Sunil','IT','Male','USA',1400)
Insert into Employees Values(7,'Hari','Payroll','Male','UK',2500)
Insert into Employees Values(8,'Sunitha','HR','Female','India',4000)
Insert into Employees Values(9,'Sneha','IT','Female','India',3000)
Insert into Employees Values(10,'Ruby','Payroll','Male','UK',4600)


Select Country, State, District, City, Sum(Sales) as TotalSales
From Sales
Group By Cube (Country, State, District, City) -- Works on maximum numbers of combinations


Select Country, State, District, City, Sum(Sales) as TotalSales
From Sales
Group By ROLLUP(Country, State, District, City)-- works based on the Hirerchy



Select Country, State, District, City, Sum(Sales) as TotalSales
From Sales
Group By Cube (Country)


Select Country, Sum(Sales) as TotalSales
From Sales
Group By ROLLUP(Country)