--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