Using Subqueries to select a Count MYSQL

Question

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);

Matt W · Accepted Answer · 2014-07-17 00:39:29Z

1

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.

edited Jul 17, 2014 at 0:39

answered Jul 17, 2014 at 0:11

Matt W

4763 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Rahul Over a year ago

Shouldn't that order by be after group by? And you need count(win).

Matt W Over a year ago

Probably. I always always screw up the order the first time, no matter how many times I use 'em. It's like plugging in a USB cable, 100% of the time, it works none of the time.

TaylorM Over a year ago

Doesn't work. I think majority of the issue is this there 4 records for every 1 match. 2 records for team A and B in game 1 and 2 more records for team A and B in game 2. So there is a possibility for a match to go either 2-0, 1-1, or 0-2. So YES I want it to group by matchId but I also want it to sum the amount of wins that specific team had in that specific match which is either 2, 1 or 0.

Matt W Over a year ago

Maybe I need to see a more complete example of your table, but you show 4 records per match, but only 2 will have a '1' in the win column. SUM(win) will give you 0, 1 or 2 per match for each team, depending upon the number of wins.

Jason Heo Over a year ago

Well, I think Matt's answer works fine with your sample data. It would be better if you post sample data which Matt's answer does not produces wrong result.

|

Collectives™ on Stack Overflow

Using Subqueries to select a Count MYSQL

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related