Skip to content

Creating a Compound NULLIF in AVG Function With SqlServer

Updated: at 11:18 PM

My use case is I’ve got a session survey column on the Silicon Valley Code Camp session evaluation page.  The results of a question like “How is this course overall” can be 0,1,2,3,4,5 where 0 means the person did not answer the question and 5 means the person chose not applicable.  I want to throw away both of these answers in my average using the SqlServer AVG function (knowing AVG does not include nulls).

With some help from StackOverflow and some creative nesting, I came up with the following Sql. It feels ugly and not scalable, but it does work.  I’m open to other suggestions but thought I’d post it anyhow as a first pass just to see what others think.  I’m including the full query so it’s clear what I’m going after.  A simple join with a where clause would work if it were just one value I’m selecting.

SELECT dbo.SessionEvals.SessionId,
AVG(NULLIF (CAST (NULLIF (Cast (dbo.SessionEvals.CourseAsWhole as Float),
5) as Float), 0)) AS CourseAsWholeAvg,
COUNT(*) as CntEvals,
when dbo.SessionEvals.InstructorPromptness = 'On Time' then 1
else null
end) AS SpeakerOnTime,
when dbo.SessionEvals.InstructorPromptness = 'Late' then 1
else null
end) AS SpeakerLate,
when dbo.SessionEvals.InstructorPromptness = 'No Show' then 1
else null
end) AS SpeakerNoShow,
when dbo.SessionEvals.PercentFull = '10% to 90%' then 1
else null
end) AS PercentFull10to90,
when dbo.SessionEvals.PercentFull = '> 90%' then 1
else null
end) AS PercentFullGreaterThan90,
when dbo.SessionEvals.PercentFull = ' < 10% Full ' then 1
else null
end) AS PercentFullLessThan10,
AVG(NULLIF (Cast (dbo.SessionEvals.EstimatedNumberAttendees as Float), 0)
) AS EstimatedAttending
FROM dbo.Sessions
INNER JOIN dbo.SessionEvals ON (dbo.Sessions.Id =
WHERE dbo.Sessions.CodeCampYearId = {0} AND

GROUP BY dbo.SessionEvals.SessionId

(CourseAsaWhole is all I’m really talking about here)