Assistance with an SQL Subquery
Assistance with an SQL Subquery
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!!
Answer by ccStars for Assistance with an SQL Subquery
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
Answer by Hercule for Assistance with an SQL Subquery
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
Answer by Aishaterr for Assistance with an SQL Subquery
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;
Answer by Gordon Linoff for Assistance with an SQL Subquery
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.
Answer by jpw for Assistance with an SQL Subquery
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.
Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72
0 comments:
Post a Comment