Assistance with an SQL Subquery

Question

So I've got a table that looks like this: (called PRIZE)

Event_id    Place   Money
101 1   120
101 2   60
101 3   30
102 1   10
102 2   5
102 3   2
103 1   100
103 2   60
103 3   40
401 1   1000
401 2   500
401 3   250
401 4   100
401 5   50

And I am trying to answer this question: 'For each event, list the prize money available for first, second and third place on one line. Group by event_id.

For eg. one row of results would appear:

Event_id   First   Second   Third
101         120      60      30

I've got this so far:

SELECT Event_id, Money AS 'First' 
FROM PRIZE 
WHERE Place = '1'
GROUP BY Event_id;

but am really having trouble adding in the SQL for the 'Second' and 'Third' parts of the question.

Can anyone help?

Thanks!!

What about place 4 and 5 for event 401? – Matt Aug 20 '15 at 10:34 — Matt, Aug 20 '15 at 10:34

score 1 · Answer 1 · answered Aug 20 '15 at 10:55

Rather than executing multiple selects with subqueries, why not execute just one select statement using multiple joins?

SELECT tb.event_id,
       tb1.money AS first,
       tb2.money AS second,
       tb3.money AS third
  FROM prize tb
       INNER JOIN prize tb1 ON tb1.event_id = tb.event_id AND tb1.place = 1
       INNER JOIN prize tb2 ON tb2.event_id = tb.event_id AND tb2.place = 2
       INNER JOIN prize tb3 ON tb3.event_id = tb.event_id AND tb3.place = 3
GROUP BY tb.event_id;

score 1 · Answer 2 · answered Aug 20 '15 at 10:58

I would approach this using conditional aggregation:

select event_id,
       sum(case when place = 1 then Money else 0 end) as place_1,
       sum(case when place = 2 then Money else 0 end) as place_2,
       sum(case when place = 3 then Money else 0 end) as place_3
from prize
where place in (1, 2, 3)
group by event_id;

Note: if there are ties, then this adds all the money for the places.

ccStars · Answer 3 · 2015-08-20T10:44:45.093

0

SELECT E.Event_ID, 
   (SELECT Money FROM PRIZE AS p1 WHERE Place = '1' AND p1.Event_ID = e.Event_ID) AS 'First',
   (SELECT Money FROM PRIZE AS p2 WHERE Place = '2' AND p2.Event_ID = e.Event_ID) AS 'Second',
   (SELECT Money FROM PRIZE AS p3 WHERE Place = '3' AND p3.Event_ID = e.Event_ID) AS 'Third'
FROM 
   (SELECT DISTINCT Event_ID
   FROM PRIZE) AS E

Hope this helps

edited Aug 20 '15 at 10:44

answered Aug 20 '15 at 10:29

ccStars

817
2
11
34

Thanks for that 'ccStars' - should this work using mySql? I've copied and pasted it in and mySQl isn't liking it.. – Fiona Aug 20 '15 at 10:36
Thanks again for your help! – Fiona Aug 20 '15 at 10:43

score 0 · Answer 4 · answered Aug 20 '15 at 10:35

If you have only one price per place and per Event_id this query should be good for your purpose.

Select Event_id
,FIRST=Max(CASE WHEN Place=1 THEN Money END)
,SECOND=Max(CASE WHEN Place=2 THEN Money END)
,THIRD=Max(CASE WHEN Place=3 THEN Money END)
from PRIZE
Group by Event_id

score 0 · Answer 5 · edited May 23 '17 at 12:16

If you just want three columns then using conditional aggregation is the easiest way to pivot the data:

select 
    event_id
    , max(case when place=1 then money end) as first
    , max(case when place=2 then money end) as second
    , max(case when place=3 then money end) as third
from 
    prize
group by 
    event_id;

If you want a dynamic solution that gives you a column per place then this query (which is adapted from this question and answer) would do that:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Place = ''',
      Place,
      ''' THEN Money END) AS `',
      Place, '`'
    )
  ) INTO @sql
FROM  prize;

SET @sql = CONCAT('SELECT Event_id, ', @sql, ' 
                  FROM prize 
                  GROUP BY Event_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This would have the numbers as column names though, if you want ordinals you could create a lookup table and join with that.

Sample SQL Fiddle with both solutions.

Assistance with an SQL Subquery

5 Answers5