04pallav
9/13/2017 - 9:52 PM

Rollup,cube,grouping sets

Rollup,cube,grouping sets

SELECT Country, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY ROLLUP(Country)

#### does group by and sums up total with row name 'null'

2.SELECT Country, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY ROLLUP(Country)

#####################################Grouping sets

1.	SELECT Country, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY GROUPING SETS
((Country),())
2.	SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY GROUPING SETS
(
(Country, Gender),
(Country),
()
)

1=3
2=4


5.SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Cube(Country, Gender)


### generates all possible combo of country and gender

6.SELECT Country, Gender, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY 
GROUPING SETS
((Country, Gender),(Country),(Gender),())

ROLLUP
A,B,C
A,B
A
()

CUBE
A,B,C
A,B
A,C
B,C
A
B
C
()