Using MySQL, I am trying to get a list of ONLY the teams that are tied (has the same number of wins) from a list of all teams in a given list. To calculate the wins for each team, I am using a SUM function while grouping by teams.
So far I have created this query:
SELECT results.DATE,
results.team1,
SUM(results.win) AS WINS,
SUM(results.lose) AS LOSSES,
SUM(results.pt_diff) AS PT_DIFF,
event_team.div_sname,
event_team.pool_name
FROM results,
event_team,
matches
WHERE results.team1 = event_team.team_id
AND results.sched_id = matches.sched_id
AND matches.match_type = 'pool'
AND event_team.div_sname = 'C4A'
AND event_team.pool_name = 'Pool_C4A_1'
AND event_team.event_id = 25
GROUP BY results.team1
ORDER BY event_team.pool_name, wins DESC, losses ASC, PT_DIFF DESC
This query returns the entire list of teams and their
DATE team1 WINS LOSSES PT_DIFF div_sname pool_name
---------- ------ ------ ------ ------- --------- ------------
2014-09-06 54 9 3 28 C4A Pool_C4A_1
2014-09-06 62 6 6 -2 C4A Pool_C4A_1
2014-09-06 55 5 7 1 C4A Pool_C4A_1
2014-09-06 59 5 7 -7 C4A Pool_C4A_1
2014-09-06 65 5 7 -20 C4A Pool_C4A_1
How do I eliminate the first two rows in the example above so that I only get team 55, 59, and 65 who are all tied with 5 wins each? Please note that I may have multiple ties in my overall results so any solution must capture all teams with ties even if the wins are different between the groups.
I have tried the following but after further reading it seems I can not GROUP BY an aggregate function. How else can I approach this?
SELECT results.DATE,
results.team1,
SUM(results.win) AS WINS,
event_team.div_sname,
event_team.pool_name
FROM results,
event_team,
matches
WHERE results.team1 = event_team.team_id
AND results.sched_id = matches.sched_id
AND matches.match_type = 'pool'
AND event_team.div_sname = 'C4A'
AND event_team.pool_name = 'Pool_C4A_1'
AND event_team.event_id = 25
GROUP BY WINS
HAVING COUNT(WINS) >1
Thanks in advance for any and all replies!
Here's a solution:
SELECT
results.DATE,
results.team1,
results.win,
event_team.div_sname,
event_team.pool_name
FROM results,
event_team,
matches
WHERE results.team1 = event_team.team_id
AND results.sched_id = matches.sched_id
AND matches.match_type = 'pool'
AND event_team.div_sname = 'C4A'
AND event_team.pool_name = 'Pool_C4A_1'
AND event_team.event_id = 25
AND results.win IN (SELECT results.win FROM results GROUP BY results.win HAVING COUNT(*)>1)
This will give you a result set that looks like this:
+------------+-------+-----+-----------+------------+
| DATE | team1 | win | div_sname | pool_name |
+------------+-------+-----+-----------+------------+
| 2014-09-06 | 55 | 5 | C4A | Pool_C4A_1 |
| 2014-09-06 | 59 | 5 | C4A | Pool_C4A_1 |
| 2014-09-06 | 65 | 5 | C4A | Pool_C4A_1 |
+------------+-------+-----+-----------+------------+
That subquery just selects all the numbers of wins where there is more than one row having that number of wins.