Select Unique Values from Different Columns

Question

I have a table that has 5 different columns:

vote_1, vote_2, vote_3, vote_4 and vote_5

For each row, these columns contain a name and what I am trying to do now is fetch the total number of occurrences of every value in any of the above columns.

The final result should be something like:

Value1 | 30

Value2 | 25

.....

How to achieve this in a single SQL query?.

Any time you have enumerated columns (above '2', say) it's probably time to start rethinking your design. (Plus, we have different definitions of 'different') — Strawberry
– Strawberry, Commented Aug 26, 2016 at 15:06

shmosel · Accepted Answer · 2016-08-28 19:54:40Z

3

Use UNION to combine multiple columns into one. Then GROUP and COUNT.

select vote, count(*) as cnt
from (
    select vote_1 as vote from mytable
    union all select vote_2 as vote from mytable
    ..
    union all select vote_N as vote from mytable
) sub
group by vote

edited Aug 28, 2016 at 19:54

shmosel

51.1k7 gold badges82 silver badges147 bronze badges

answered Aug 26, 2016 at 14:55

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

1 Comment

shmosel Over a year ago

@ArtemisChaitidis You can thank him by selecting this as the correct answer.

Collectives™ on Stack Overflow

Select Unique Values from Different Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related