Vipar Vipar - 4 months ago 9
SQL Question

Need to Include Count even if another column is 0

I am trying to get some Average scores from User Satisfaction Surveys where I work. Some times there are 0 ratings for a question because a service was not offered on the flight that we did so they can't answer. I didn't really consider the scenario however, where a flight only has a single survey which means that I might end up with 0 ratings. Underneath you see a diagram of the database:

enter image description here

This is a problem because if a survey has 13 questions and I only get results back for 8 of them because 5 have 0 ratings, then I end up with a mismatch of data in the end where all the other flights have all 13 questions answered at least once (hope that makes sense!)

So I used to do this:

SELECT AVG(CAST(RATING AS NUMERIC(18, 2))) AS AVERAGE,
QUESTIONID,
COUNT(QUESTIONID) AS COUNT
FROM AnswersRating
WHERE SURVEYID IN
(
SELECT ID
FROM SURVEYS
WHERE FLIGHTDATAID = 7311
) AND RATING > 0
GROUP BY QUESTIONID
ORDER BY QUESTIONID;


Which is fine as long as you don't have only 1 survey for a flight. But it turns out we do:

AVERAGE QUESTIONID COUNT
3.000000 109 1
3.000000 110 1
3.000000 111 1
3.000000 112 1
3.000000 115 1
3.000000 117 1
3.000000 118 1
3.000000 120 1
3.000000 121 1


This flight does not have answers for questions 113, 114, 116 and 119. I know this because I know this survey have 13 questions. But a survey can have a variable number of questions in the future. So what I would like to have instead of the above would be this:

AVERAGE QUESTIONID COUNT
3.000000 109 1
3.000000 110 1
3.000000 111 1
3.000000 112 1
0.000000 113 0
0.000000 114 0
3.000000 115 1
0.000000 116 0
3.000000 117 1
3.000000 118 1
0.000000 119 0
3.000000 120 1
3.000000 121 1


Where Count is 0 on some of these. Is that possible to do, given my current Query and if so, how?

Answer

since null adds zero to a count, do something like

...SUM(CAST(RATING AS NUMERIC(18, 2))) / COUNT(CASE WHEN RATING > 0 THEN 1 ELSE NULL END) 
        AS TheAdjustedAverage...

you might eventually have to wrap that in a CASE itself to deal with a case where all ratings are zero