CODEWITHSUNDEEP
mysqlsqlselect
Razgriz
Razgriz

Reputation: 7343

Selecting Distinct Entries across columns?

I have a database that tracks the prizes that people win across the booths of a fair. Most of the booths have overlapping and similar prizes, so a participant can win a Pillow (for example) from 2 booths.

Now, we want to be able to track how many people won a given prize, since we want to tally and account how many people won a certain prize already.

Here is how the table looks like:

table

It's easy to get the count of a certain prize in a given column. However, I'm having problems trying to consolidate the data across multiple columns.

Upvotes: 1

Views: 31

Answers (1)

Mureinik
Mureinik

Reputation: 311853

If you can't normalize your table, a bunch of union alls could do the trick:

SELECT   prize, COUNT(*)
FROM     (SELECT station_1_prize AS prize FROM mytable
          UNION ALL
          SELECT station_2_prize AS prize FROM mytable
          UNION ALL
          -- Etc...
         ) t
GROUP BY prize

Upvotes: 4

Related Questions