Blog coding and discussion of coding about JavaScript, PHP, CGI, general web building etc.

Thursday, May 26, 2016

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

Popular Posts

Powered by Blogger.