There is syntax that makes quick and dirty aggregate reporting much quicker and less dirty. The syntax is comprised of the GROUPING SETS clause and two functions: GROUPING_ID and GROUPING. Below is some SQL demonstrating GROUPING SETS and the GROUPING function.
Let's imagine we want to quickly report out a distinct count of all juniors and seniors by full-time and part-time status. With one query we can create a nicely formatted report:
select isnull(cs.Class, '--Total--') as Class,
case when cs.Class is null then ''
else isnull(e.[Description], '--Total--') end as Enrollment,
count(distinct sh.StudentId) as Headcount
from DataMart.dbo.StudentHistory as sh
join DataMart.dbo.ClassStatus as cs
on sh.ClassStatusId = cs.ClassStatusId
join DataMart.dbo.Enrolled as e
on e.EnrolledId = sh.EnrolledId
where sh.RecordType = 'F'
and sh.TermId = 20113
and sh.ClassCode in ('JR','SR')
and sh.EnrolledCode in ('F','P')
group by grouping sets
(
(cs.Class, e.[Description]),
(cs.Class),
()
)
order by grouping(cs.Class), cs.Class,
grouping(e.[Description]),e.[Description]
Another query worth looking at is one that summarizes by College, Dept, and Major:
;with cte_CollegeMajor as
(
select m.MajorId, m.Major, m.MajorCode, m.[Description] as MajorDescription, m.PreMajorFor,
prog.ProgName as Program, d.[Description] as Department, a.AreaName as Area,
c.[Description] as College, c.Abbreviation as CollegeAbbreviation
from DataMart.dbo.Major m
join DataMart.dbo.Program prog
on m.ProgramID = prog.ProgramID
join DataMart.dbo.Department d
on prog.DepartmentID = d.DepartmentId
join DataMart.dbo.Area a
on d.AreaID = a.AreaID
join DataMart.dbo.College c
on c.CollegeId = a.CollegeID
where m.Active = 1
and d.Active <> 'N'
)
select isnull(cm.College, '--Overall Total--') as College, isnull(cm.Department, '--Total--') as Department,
case when cm.Department is null then '' else isnull(cm.Major, '--Total--') end as Major,
count(distinct sm.StudentId) as Headcount
from DataMart.dbo.StudentMajor as sm
join cte_CollegeMajor cm
on sm.MajorId = cm.MajorId
where cm.CollegeAbbreviation in ('BU','LA')
and sm.RecordType = 'F'
and sm.TermId = 20113
group by grouping sets
(
(cm.College, cm.Department, cm.Major),
(cm.College, cm.Department),
(cm.College),
()
)
order by grouping(cm.College), cm.College, grouping(cm.Department), cm.Department,
grouping(cm.Major), cm.Major