Standard Deviations with CTE in SQL

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:


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

This entry was posted in Syndication, T-SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s