Rollup and Cube

PHOTO EMBED

Wed Mar 23 2022 08:36:44 GMT+0000 (Coordinated Universal Time)

Saved by @Bambibo9799

--using rollup
select coalesce(department,'All Department Count'),
coalesce(jobrole,'All Jobrole Count'), count(*) 
from masterdataset as m 
group by rollup(department ,jobrole) -- department is the hierachy = department > jobrole
order by department, count(*) desc 

--using cube
select coalesce(department,'All Department Count'),
coalesce(jobrole,'All Jobrole Count'), count(*) 
from masterdataset as m 
group by cube(department,jobrole)
order by department
content_copyCOPY