I was working with a client recently getting the number of standard deviations a student was off the average number of suspensions. I accomplished this with two Common Table Expressions, (CTE).

The first CTE gets the standard deviation and the average for the entire database. The Database holds all of the schools in the district and this CTE gives use the overall average.

The second CTE gets the number of suspensions summed up and group by student.

In the final query we bring the two CTE’s together by selected each column and performing the calculation to determine the number of standard deviations the student is off from the average (Mean).

That calculation is: (Student Count – Overall Average)/Standard Deviation

There are other ways to do this same thing. But by breaking it up into two CTE’s it make the maintenance and testing very easy. You can run the CTE’s separately to make sure each is returning the correct data before combining them.

Here is the query:

With

School as( — This CTE gets the Standard Deviation and Average of the Districts ISS

Select convert(numeric(18,3),stdev(f.InSchoolSuspension)) StdISS, AVG(convert(numeric(18,3),f.InSchoolSuspension)) AvgISS

from FactSchoolPerson f

),

Student as( — This CTE gets the ISS summed up by student

Select f.DimDWPersonKey, Sum(f.InSchoolSuspension) ISS

from FactSchoolPerson f

Group by f.DimDWPersonKey

)–Combine the two CTEs in this query and calculate the number of StdDevs Off the Mean

Select st.DimDWPersonKey, st.ISS, s.AvgISS , s.StdISS ,

((st.ISS – s.AvgISS)/ s.StdISS) StdOffISS

from student st Cross Join

School s

Order by st.DimDWPersonKey