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)