Aggregate Query

use ReportBuilderTraining

--
-- unduplicted counts
--

select sm.TermId, max(t.TermName) Term, d.Department, count(sm.StudentId) Duplicated, count(distinct sm.StudentId) Students
	from StudentMajor sm
	join Major m
	on sm.MajorId=m.MajorId
	join MajorProgram mp
	on m.ProgramID=mp.ProgramID
	join Department d
	on mp.DepartmentID=d.DepartmentID
	join Term t
	on sm.TermId=t.TermId
	group by sm.TermId, d.Department
	order by sm.TermId, d.Department



select s.StudentId
	,count(*)
	,count(a.AddressId)
	,count(case when a.AddressTypeCode='P' then AddressId end) PermanentAddresses
	from Student s
	left join Address a
	on s.StudentId=a.StudentId
	group by s.StudentId
	having count(*)=0 or count(a.AddressId)=0


select s.StudentId
	from Student s
	left join Address a
	on s.StudentId=a.StudentId
	group by s.StudentId
	having count(case when a.AddressTypeCode='P' then AddressId end) = 0