CODEWITHSUNDEEP
mysqlsql
kgrondell
kgrondell

Reputation: 177

Select count of similar values in multiple columns

I have a table for user input to questions. They were given the option to give up to five answers to the same question so you have 5 fields with up to five different answers for the same question.

So field1,filed2,field3,field4,field5 could all have the answer "foo"

I want create a query that will return a number count of the total answers in any of the five columns for all users for each possible answer.

So the output would be:

'foo' 22 'I like cake' 32 'who cares' 2

I am on the right track with the following but I suspect there is a more elegant solution. Here is what I have so far:

SELECT field1,filed2,field3,field4,field5,
         (sum(CASE WHEN field1='foo' THEN 1 ELSE 0 END) +
         sum(CASE WHEN field2='foo' THEN 1 ELSE 0 END)) +
         sum(CASE WHEN field3='foo' THEN 1 ELSE 0 END)) +
         sum(CASE WHEN field4='foo' THEN 1 ELSE 0 END)) +
         sum(CASE WHEN field5='foo' THEN 1 ELSE 0 END)) AS count

   FROM items
   GROUP BY field1,filed2,field3,field4,field5
  ;

Any suggestions would be appreciated.

Upvotes: 0

Views: 44

Answers (1)

jarlh
jarlh

Reputation: 44786

Perhaps this is what you need. Have a derived table that returns all columns UNION ALL-ed into one. Then do the GROUP BY:

select field, count(*)
from
(
    select field1 as field from items
    union all
    select field2 from items
    union all
    ...
    select fieldn from items
) dt
group by field

Upvotes: 1

Related Questions