MySQL - 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:


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.

1 answer

  • answered 2018-03-13 21:37 Mureinik

    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