CODEWITHSUNDEEP
mysqlsqljoin
Lavandil
Lavandil

Reputation: 153

Getting resulting data in one row from 3 select queries

I have tables :

players:

id
name

places:

id
playerId
tournamentId

tournaments: 

id
name
prize1
prize2
prize3

Query: 

(
    select pl.Name, pla.place,sum(t2.prize1),count(t2.name) 
    from players pl 
    inner join places pla on pl.id = pla.PlayerId
    inner join tournaments2 t2 on pla.tournamentId = t2.id 
    where pla.place = 1 
    group by pl.name
) union (
    select pl.Name, pla.place,sum(t2.prize2),count(t2.name) 
    from players pl 
    inner join places pla on pl.id = pla.PlayerId
    inner join tournaments2 t2 on pla.tournamentId = t2.id 
    where pla.place = 2 group by pl.name
) union (
    select pl.Name, pla.place,sum(t2.prize3),count(t2.name) 
    from players pl 
    inner join places pla on pl.id = pla.PlayerId
    inner join tournaments2 t2 on pla.tournamentId = t2.id 
    where pla.place = 3 group by pl.name
)

Result: 

 Name       place  gold     placecount
"player1"   "1"   "1500"    "2"
"player2"   "1"   "500"     "1"
"player1"   "2"   "300"     "1"
"player1"   "3"   "200"     "1"

Desired data representation: 

 name     gold1stplace 1stplacecount gold2ndplace 2ndplacecount gold3rdplace 3rdplacecount
"player1"    "1500"             "3"         "300"         "1"         "200" "1"

How can I achive desired data representation?

Upvotes: 0

Views: 40

Answers (1)

GMB
GMB

Reputation: 222482

It should be possible to solve this using conditional aggregation :

SELECT
    p.name player,
    SUM(CASE WHEN pc.place = 1 THEN t.prize1 ELSE 0 END) gold1stplace,
    SUM(CASE WHEN pc.place = 1 THEN 1        ELSE 0 END) place1stcount,
    SUM(CASE WHEN pc.place = 2 THEN t.prize2 ELSE 0 END) gold2ndplace,
    SUM(CASE WHEN pc.place = 2 THEN 1        ELSE 0 END) place2ndcount,
    SUM(CASE WHEN pc.place = 3 THEN t.prize3 ELSE 0 END) gold3rdplace,
    SUM(CASE WHEN pc.place = 3 THEN 1        ELSE 0 END) place3rdcount
FROM 
    players p
    INNER JOIN places pc ON pc.playerId = p.id
    INNER JOIN tournaments t ON t.id = pc.tournamentId
GROUP BY p.name

Upvotes: 2

Related Questions