select distinct a.AssessmentFamily,a.AssessmentTitle as Title,a.AssessmentVersion as [Version],a.AssessmentForm as Form,a.MaxRawScore,a.AdaptiveAssessment as Adaptive
,dc.CodeValue as AssessmentCategory
,(select count(*) from edfi.StudentAssessment sa where sa.AssessmentIdentifier=a.AssessmentIdentifier) as Assessments
,(select count(*) from edfi.StudentAssessmentScoreResult sa where sa.AssessmentIdentifier=a.AssessmentIdentifier) as AssessmentScoreResults
,(select count(*) from edfi.StudentAssessmentPerformanceLevel sa where sa.AssessmentIdentifier=a.AssessmentIdentifier) as AssessmentPerfLevel
,(select count(*) from edfi.StudentAssessmentStudentObjectiveAssessmentScoreResult sa where sa.AssessmentIdentifier=a.AssessmentIdentifier) as AssessmentObjScoreResults
,(select count(*) from edfi.StudentAssessmentStudentObjectiveAssessmentPerformanceLevel sa where sa.AssessmentIdentifier=a.AssessmentIdentifier) as AssessmentObjPerfLevel
from edfi.Assessment a
left join edfi.Descriptor dc on dc.DescriptorId=a.AssessmentCategoryDescriptorId
order by a.AssessmentFamily, a.AssessmentVersion, a.AssessmentTitle