i trying average scores user satisfaction surveys work. times there 0 ratings question because service not offered on flight did can't answer. didn't consider scenario however, flight has single survey means might end 0 ratings. underneath see diagram of database:
this problem because if survey has 13 questions , results 8 of them because 5 have 0 ratings, end mismatch of data in end other flights have 13 questions answered @ least once (hope makes sense!)
so used this:
select avg(cast(rating numeric(18, 2))) average, questionid, count(questionid) count answersrating surveyid in ( select id surveys flightdataid = 7311 ) , rating > 0 group questionid order questionid; which fine long don't have 1 survey flight. turns out 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 not have answers questions 113, 114, 116 , 119. know because know survey have 13 questions. survey can have variable number of questions in future. have instead of above 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 0 on of these. possible do, given current query , if so, how?
since null adds 0 count,
...sum(cast(rating numeric(18, 2))) / count(case when rating > 0 1 else null end) theadjustedaverage... glad helps!
you might have wrap in case deal case ratings zero
you might like
case when count(case when rating > 0 1 else null end) = 0 null else sum(cast(rating numeric(18, 2))) / count(case when rating > 0 1 else null end) end theadjustedaverage 
Comments
Post a Comment