CODEWITHSUNDEEP
mysqlsubquery
TaylorM
TaylorM

Reputation: 109

Using Subqueries to select a Count MYSQL

I am keeping data of Best-of-Two series. So for every match there is 2 games associated with the same teams.

So this means for every match there is two games and the table holds records of each game for each team.

So for every 1 match there is 4 records (Game 1 of Team A, Game 2 of Team A, Game 1 of Team B and Game 2 of Team B). A match has 3 possible outcomes : 2-0, 1-1 or 0-2.

EXAMPLE : Team A plays 3 matches vs Team B, C and D going (2-0, 2-0 and 1-1) My desired outcome from the select statement is this:

matchId | Team | Wins 
1       |  A   |  2
1       |  B   |  0
2       |  A   |  2
2       |  C   |  0
3       |  A   |  1
3       |  D   |  1

ACTUAL DATA :

matchId | gameId |teamName | Victory | Loss
2959      3635      Frost       0       1
2959      3490      Frost       1       0
2959      3490      SKT         0       1
2959      3635      SKT         1       0
2956      3487      Frost       0       1
2956      3573      Frost       0       1
2956      3573      Shield      1       0
2956      3487      Shield      1       0
2923      3452      SKT         1       0
2923      3507      SKT         1       0
2923      3507      IM1         0       1
2923      3452      IM1         0       1

So with this code I'm looking for :

matchId |teamName | Wins
2959      Frost       1
2959      SKT         1
2956      Frost       0
2956      Shield      2
2923      SKT         2
2923      IM1         0

This is my current code as an attempt to figure it out:

SELECT DISTINCT b.matchId, c.teamName, d.Wins
FROM PoolOfTeams a JOIN (
    SELECT t1.matchId, t1.gameId, t1.teamName FROM poolOfTeams t1
) b on (b.matchId = a.matchId) JOIN (
    SELECT t2.gameId, t2.teamName FROM poolOfTeams t2
) c ON (c.gameId = b.gameId) JOIN (
    SELECT t3.teamName, SUM(t3.matchVictory) AS Wins
    FROM poolOfTeams t3 group by matchId
) d ON (d.teamName = c.teamName);

Upvotes: 1

Views: 44

Answers (1)

Matt W
Matt W

Reputation: 476

Try

SELECT   matchID, Team, SUM(Win) AS Wins
FROM     poolOfTeams
GROUP BY matchID, Team
ORDER BY Team ASC

I think 'GROUP BY' is what you are looking for.

Edit: swapped the order of the GROUP and ORDER statements. Thanks Rahul.

Upvotes: 1

Related Questions