Need to Include Count even if another column is 0

Question

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:

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 1

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...

Glad it helps!

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

you might do something like

CASE WHEN COUNT(CASE WHEN RATING > 0 THEN 1 ELSE NULL END)  = 0 THEN

    NULL 

ELSE 

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


END AS TheAdjustedAverage

Answer 2

Please check following Select statement

SELECT AVG(CAST(ISNULL(RATING,0) AS NUMERIC(18, 2))) AS AVERAGE,
     QUESTIONID,
     COUNT(QUESTIONID) AS COUNT
FROM SurveyQuestions Q
LEFT JOIN AnswersRating A on Q.SURVEYID = A.SURVEYID
WHERE SURVEYID IN
(
   SELECT ID
   FROM SURVEYS
   WHERE FLIGHTDATAID = 7311
) --AND RATING > 0
GROUP BY QUESTIONID
ORDER BY QUESTIONID;

Note that I assumed, I use a table named SurveyQuestions which has all questions asked to the user. The user can either answer or pass the question without answering it.

By using LEFT JOIN to AnswersRating table, I got all questions with Rating value supplied and questions without answer, so the Rating is NULL

So in SELECT list, I use ISNULL() function to map unanswered question ratings to 0

I hope it helps,

Answer 3

Try This: you have RATING > 0 outside the Surveys table condition, needs to be inside if I got it well:

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;