select sch.SchoolId,eo.StateOrganizationId,eo.NameOfInstitution
,(select count(*) from edfi.StudentSchoolAssociation ssa where ssa.SchoolId=sch.SchoolId) as StudentEnrollments
,(select count(*) from edfi.StaffSchoolAssociation ssa where ssa.SchoolId=sch.SchoolId) as StaffAssigned
,(select count(*) from edfi.Section sec where sec.SchoolId=sch.SchoolId) as Sections
,(select count(*) from edfi.StudentSectionAssociation ssa where ssa.SchoolId=sch.SchoolId) as StudentSection
,(select count(*) from edfi.StaffSectionAssociation ssa where ssa.SchoolId=sch.SchoolId) as StaffSection
,(select count(*) from edfi.StudentAssessment sa
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=sa.StudentUSI
where sa.AssessmentTitle like 'Acadience Reading%' and ssa.SchoolId=sch.SchoolId) as Acadience
,(select count(*) from edfi.StudentAssessment sa
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=sa.StudentUSI
where sa.AssessmentTitle like 'aimswebPlus%' and ssa.SchoolId=sch.SchoolId) as AimsWebPlus
,(select count(*) from edfi.StudentAssessment sa
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=sa.StudentUSI
where sa.AssessmentTitle like 'WBM%' and ssa.SchoolId=sch.SchoolId) as NWEA
,(select count(*) from edfi.StudentAssessment sa
left join edfi.StudentSchoolAssociation ssa on ssa.StudentUSI=sa.StudentUSI
where sa.AssessmentTitle like 'Star%' and ssa.SchoolId=sch.SchoolId) as Star
from edfi.School sch
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=sch.SchoolId
order by eo.NameOfInstitution