Weighted Average of Survey Questions

I have to calculate weighted average for all questions.

Assuming there are 22 questions in a survey which has 5 options as 1,2,3,4,5 and NA.

For example,

3 people took the survey (all 22 question)

Assuming there are 10 NA responses (combined for all 3).

So, denominator will be : 22*3 – 10 = 56

Numerator will be : 1*X1 + 2*X2 + 3*X3 + 4*X4 + 5*X5, wherein, X1 + X2 + X3 + X4 + X5 = 56

How can I implement in SQL ?

Answer

You seem to be describing something like:

select avg(case when r.response = '1' then 1
                when r.response = '2' then 2
                when r.response = '3' then 3
                when r.response = '4' then 4
                when r.response = '5' then 5
           end)  
from responses r
where r.response <> 'N/A'