CODEWITHSUNDEEP
mysql
Chambo
Chambo

Reputation: 9

To write and execute sub-queries in SQL

I have this table:

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

I'm trying to write and execute SQL sub-queries.So far I have come up with this but I just can't seem to finish it off to get the outcome I want.

select event_id,
(select money from prize where ) as First, 
(select money from prize where ) as Second, 
(select money from prize where ) as Third
from prize AS prize2
group by event_id;

This is my expected outcome:

Event_id    First   Second  Third
101         120     60      30

Upvotes: 0

Views: 67

Answers (4)

A.D.
A.D.

Reputation: 1180

Assuming you only want the first, second, and third to have the following result:

Event_id|First|Second|Third|
--------|-----|------|-----|
     101|  120|    60|   30|
     102|   10|     5|    2|
     103|  100|    60|   40|
     401| 1000|   500|  250|

You can skip subqueries and use if and sum. Here is what you can aim for:

SELECT Event_id
, SUM(if (place = 1, money, null)) AS `First`
, SUM(if (place = 2, money, null)) AS `Second`
, SUM(if (place = 3, money, null)) AS `Third`
FROM prize
GROUP by Event_id

Now a little explanation. For each row, you extract if the money should be in the first, second, or third column. When you have those value extracted, you can group by Event_id and sum all values.

If you need more values, you just need to add extra columns.

Upvotes: 1

Harshwardhan Sharma
Harshwardhan Sharma

Reputation: 261

Try this, May be it can help

SELECT Event_id,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(money), ',',1), ',', -1) AS First, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(money), ',',2), ',', -1) AS Second,
    SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(money), ',',3), ',', -1) AS Third
FROM `prize` WHERE place in (1,2,3) GROUP BY Event_id order By Event_id ASC

Upvotes: 0

Mangesh Auti
Mangesh Auti

Reputation: 1153

If you have fixed 3 columns then use this 

select prize2.event_id,
 (select money from prize where event_id= prize2.event_id order by place limit 0,1) as First, 
 (select money from prize where event_id= prize2.event_id order by place limit 1,1) as Second, 
 (select money from prize where event_id= prize2.event_id order by place limit 2,1) as Third
from prize AS prize2
group by event_id;

DEMO

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17615

Assuming place equates to first,second,third etc then correlated sub queries might be what you are looking for

select event_id,
(select money from t t1 where place = 1 and t1.event_id = t.event_id ) as First, 
(select money from t t1 where place = 2 and t1.event_id = t.event_id ) as Second, 
(select money from t t1 where place = 3 and t1.event_id = t.event_id ) as Third
from t
group by event_id;

+----------+-------+--------+-------+
| event_id | First | Second | Third |
+----------+-------+--------+-------+
|      101 |   120 |     60 |    30 |
|      102 |    10 |      5 |     2 |
|      103 |   100 |     60 |    40 |
|      401 |  1000 |    500 |   250 |
+----------+-------+--------+-------+
4 rows in set (0.00 sec)

But this is an invalid use of group by since there is no aggregation going on. And you would need to apply a where clause to limit to even_id 101.

Upvotes: 2

Related Questions