Show multiple categories for each event

Question

I have a prize table with 3 columns:

| event_id | place | money |

I am trying to get a result to appear with event_id, and first, second, third prize.

So far I have tried this:

SELECT event_id, (SELECT money AS first FROM prize WHERE place = 1)
FROM prize

But I get the error that the subquery returns more than 1 row.

`select event_id, (CASE WHEN place = 1 THEN money END) as First FROM prize` Maybe you want this query — Mihai, Aug 03 '15 at 16:04
Sounds like you want to `PIVOT` your data. Check out this question for an example: [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table). — Josien, Aug 03 '15 at 16:08
Did you find something that works for you? Or were you looking for more information on why your query does not work? — Josien, Aug 07 '15 at 09:45

score 1 · Answer 1 · edited May 23 '17 at 12:03

If you are quite sure that there is only one entry for each event & place combination, you can also use a combination of SUM and the IF function:

SELECT event_id
       ,SUM(IF(place=1, money, 0)) As 'FirstPlace'
       ,SUM(IF(place=2, money, 0)) As 'SecondPlace'
       ,SUM(IF(place=3, money, 0)) As 'ThirdPlace'
FROM   prize
GROUP BY event_id;

The IF function is used to make sure the correct amount of money for the correct place is returned. (It checks the truth of the first expression and returns the second expression if the first is true, and the third expression if the first is false.)

See this SQL Fiddle (adapted from McAdam331's answer).

score 0 · Answer 2 · edited Aug 07 '15 at 12:31

Your question is a little vague, so assuming that your table has the following columns: event_id, place, money you could do a query as follows:

SELECT event_id
       ,case
         when place = 1 then "first"
         when place = 2 then "second"
         when place = 3 then "third"
       end as place
       ,money
from Prize
GROUP by event_Id, place

Otherwise, could you provide more details such as table structure and example records?

Please note I've not tested the SQL

Edit - The reson your getting that error is because your subquery is not limiting by an event, only a place. So all first place results are being returned, not grouped by an event

score 0 · Accepted Answer · answered Aug 03 '15 at 16:19

If you were to break this up, you can get the first place for each event like this:

SELECT *
FROM prize
WHERE place = 1;

You could do the same for second place, and use a join to the original table to get the second place values like this:

SELECT t1.event_id, t1.money AS first
FROM(
   SELECT event_id, money
   FROM prize
   WHERE place = 1) t1
JOIN(
   SELECT event_id, money
   FROM prize
   WHERE place = 2) t2 ON t2.event_id = t1.event_id
JOIN(
   SELECT event_id, money
   FROM prize
   WHERE place = 3) t3 ON t3.event_id = t1.event_id;

Here is an SQL Fiddle example. Note that this will break if an event doesn't have at least first, second, and third. If there can be fewer rows, I would use an outer join. This may also return unexpected results if there are multiple first place rows for an event.

Show multiple categories for each event

3 Answers3