Creating a Compound NULLIF in AVG Function With SqlServer

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,
COUNT(case
when dbo.SessionEvals.InstructorPromptness = 'On Time' then 1
else null
end) AS SpeakerOnTime,
COUNT(case
when dbo.SessionEvals.InstructorPromptness = 'Late' then 1
else null
end) AS SpeakerLate,
COUNT(case
when dbo.SessionEvals.InstructorPromptness = 'No Show' then 1
else null
end) AS SpeakerNoShow,
COUNT(case
when dbo.SessionEvals.PercentFull = '10% to 90%' then 1
else null
end) AS PercentFull10to90,
COUNT(case
when dbo.SessionEvals.PercentFull = '> 90%' then 1
else null
end) AS PercentFullGreaterThan90,
COUNT(case
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 =
dbo.SessionEvals.SessionId)
WHERE dbo.Sessions.CodeCampYearId = {0} AND

GROUP BY dbo.SessionEvals.SessionId

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

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Follow

Get every new post delivered to your Inbox

Join other followers: