Dupe count on complex query
Posted by:
B R
Date: March 17, 2024 10:25PM
This is my query, the 'appearances' count is correct however the sum of conversions is wrong.
I have one game row, I have a joining table gamedata row where the gameId is the index, along with playerId and a int field for conversions.
The games are placed into roundgames, then rounds and finally competitions.
Some games may appear in multiple roundgames, rounds and competition as the single game is counted towards multiple competitions.
As I said my count gameId is correct, but conversions is counting the conversions twice when it's in two competitions.
It works if I take out the roundgame join onwards but I need information from the competition table which should be the same over all competitions.
Any ideas?
SELECT DISTINCT g.gameId, gd.playerId, t.teamName, t.teamId,
COUNT(DISTINCT gd.gameId) as Appearances,
COUNT(DISTINCT g.gameId, CASE WHEN g.isRanfurly = 1 THEN 1 END) as TTLTests,
SUM(gd.tries) as Tries,
SUM(gd.conversions) as Cons,
SUM(gd.dropGoals) as DG,
SUM(gd.penalties) as Pens,
SUM(gd.gamePts) as PTS,
IF(gd.homeaway = 1, g.team1Id, g.team2Id) as myteamId
FROM `gamedata` gd
left outer JOIN `games` g ON g.gameId = gd.gameId
JOIN `teams` t ON t.teamId = IF(gd.homeaway = 1, g.team1Id, g.team2Id)
JOIN `roundgames` rg ON rg.gameId = gd.gameId
JOIN `rounds` r ON r.roundId = rg.roundId
JOIN `competitions` c ON r.competitionId = c.competitionId
WHERE `playerId` = 95 AND `didntPlay` = 0 AND t.teamType = 0 AND g.isCompleted = 1 AND YEAR(g.kickOff) = 2015
group by c.seasonLabel, gd.playerId, t.teamName
order by c.season, seasonLabel