I have survey responses in a SQL database. Scores are 1-5.
Current format of the data table is this:
Survey_id, Question_1, Question_2, Question_3
sum(iif(Question_1 =5,1,0)) + sum(iif(Question_2=5,1,0)) .... as total5s
sum(iif(Question_1=4,1,0)) + sum(iif(Question_2=4,1,0)) .... as total4s
You have to unpivot the data and calculate the % responses thereafter. Because there are a limited number of questions, you can use
union all to unpivot the data.
select 100.0*count(case when question=4 then 1 end)/count(*) as pct_4s from (select survey_id,question_1 as question from tablename union all select survey_id,question_2 from tablename union all select survey_id,question_3 from tablename ) responses
Another way to do this could be
select 100.0*(count(case when question_1=4 then 1 end) +count(case when question_2=4 then 1 end) +count(case when question_3=4 then 1 end)) /(3*count(*)) from tablename
unpivot as @Dudu suggested,
with unpivoted as (select * from tablename unpivot (response for question in (question_1,question_2,question_3)) u ) select 100.0*count(case when response=4 then 1 end)/count(*) from unpivoted