MySQL Forums
Forum List  »  Newbie

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

Options: ReplyQuote

Written By
Dupe count on complex query
March 17, 2024 10:25PM
March 18, 2024 12:04PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.