Select count of similar values in multiple columns

Question

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.

Add some sample table data, and the expected result. (As well formatted text.) — jarlh
– jarlh, Commented Sep 30, 2016 at 12:23

jarlh · Accepted Answer · 2016-09-30 12:33:21Z

1

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

answered Sep 30, 2016 at 12:33

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shushil Bohara Over a year ago

Need to add where condition as well for each selection of 'foo'.

kgrondell Over a year ago

Interesting. I will incorporate this and see what I get. Looks close.

jarlh Over a year ago

@Suraz, wasn't that just an example? If needed, add a WHERE clause just before the GROUP BY.

Collectives™ on Stack Overflow

Select count of similar values in multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related