CODEWITHSUNDEEP
sql-server
Michael Robinson
Michael Robinson

Reputation: 1132

SQL Server: conditional aggregation

I have this table:

enter image description here

What I want to do is aggregate these so each instructor has one line, so I used this SQL:

Select 
    TermCode, SubjectCode, course, QuestionNbr, InstructorName,    
    Sum(TotalStudents) as TotalStudents, Avg(Mean) as Mean, 
    StDev(StdDev) as StdDev
From 
    @MyTable
Group By 
    TermCode, SubjectCode, course, QuestionNbr, InstructorName

And I get this:

enter image description here

The problem is that any instructor with just one entry will have a null StdDev, which is to be expected. What I want is in those cases to use the StdDev value from the original table, so I would get this:

enter image description here

Is there a way to do this?

Upvotes: 2

Views: 456

Answers (1)

user359040
user359040

Reputation:

One approach would be to use COALESCE with an aggregate function that won't return NULL - such as Max:

COALESCE(StDev(StdDev),Max(StdDev)) as StdDev

Upvotes: 2

Related Questions