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.

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