user45867 user45867 - 4 years ago 114
SQL Question

Count instances of value (say, '4') in several columns/ rows

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
383838, 1,1,1
392384, 1,5,4
393894, 4,3,5


I'm running a new query where I need % 4's, % 5's ... question doesn't matter, just overall.

At first glance I'm thinking

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


But I am unsure if this is the quickest or most elegant way to achieve this.

EDIT: Hmm on first test this query already appears not to work correctly

EDIT2: I think I need sum instead of count in my example, will edit.

vkp vkp
Answer Source

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

With 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download