CODEWITHSUNDEEP
sql-server
Peter Kellner
Peter Kellner

Reputation: 15498

How To Get Non-Null,Non-0 for Average with SqlServer Select

**Note: I need to go a little further and add NULLIF(0 or 5). I wrote a short post about my answer here: http://peterkellner.net/2013/10/13/creating-a-compound-nullif-in-avg-function-with-sqlserver/ but am not happy with my solution)

I've got a table with results where attendees type in estimated attendance to a course. If they type 0 or leave it empty, I want ignore that and get the average of values typed in. I can't figure out how to add that constraint to my AVG function without having a where clause for the entire SQL. Is that possible? My code looks like this: (EstimatedNumberAttendees is what I'm going after).

SELECT dbo.SessionEvals.SessionId,
   AVG(Cast (dbo.SessionEvals.CourseAsWhole as Float)) AS CourseAsWholeAvg,
   COUNT(*),
   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 = 'NoShow' 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(Cast (dbo.SessionEvals.EstimatedNumberAttendees as Float)) AS
   EstimatedAttending
FROM dbo.Sessions
 INNER JOIN dbo.SessionEvals ON (dbo.Sessions.Id =
 dbo.SessionEvals.SessionId)
WHERE dbo.Sessions.CodeCampYearId = 8
GROUP BY dbo.SessionEvals.SessionId

Upvotes: 0

Views: 93

Answers (3)

Vasanth
Vasanth

Reputation: 1710

SQL AVG function will by default ignore null values so you need to only exclude the 0s. Your AVG code can be changed to below:

AVG(nullif( Cast(dbo.SessionEvals.CourseAsWhole as Float), 0) AS CourseAsWholeAvg

Upvotes: 0

Andriy M
Andriy M

Reputation: 77677

AVG omits NULLs. Therefore make it treat 0s as NULLs. Use NULLIF for that:

...
AVG(NULLIF(Cast (dbo.SessionEvals.CourseAsWhole as Float), 0)) AS CourseAsWholeAvg,
...
AVG(NULLIF(Cast (dbo.SessionEvals.EstimatedNumberAttendees as Float), 0)) AS EstimatedAttending
...

Upvotes: 2

Szymon
Szymon

Reputation: 43023

You can try to use an inner query to get the same sessions but exclude zero and null:

SELECT dbo.SessionEvals.SessionId,
   (
        SELECT AVG(SE1.CourseAsWhole)
        FROM dbo.SessionEvals SE1
        WHERE SE1.SessionId = dbo.SessionEvals.SessionId
        AND ISNULL(SE1.CourseAsWhole, 0) <> 0
   ) AS CourseAsWholeAvg,
   COUNT(*),
   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 = 'NoShow' 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(Cast (dbo.SessionEvals.EstimatedNumberAttendees as Float)) AS
   EstimatedAttending
FROM dbo.Sessions
 INNER JOIN dbo.SessionEvals ON (dbo.Sessions.Id =
 dbo.SessionEvals.SessionId)
WHERE dbo.Sessions.CodeCampYearId = 8
GROUP BY dbo.SessionEvals.SessionId

Upvotes: 0

Related Questions