Re: Dupe count on complex query

[B R]

Phillip Ward Wrote:
-------------------------------------------------------
> "Select Distinct" is [almost] always a Red Flag
> for me, especially where multiple Joins are
> involved. The "need" for it almost always
> indicates incorrect join conditions or data
> structure that are not correctly normalised.
> "Distinct" and "Group By" is definitely a
> No-No.
>
> Without seeing your table structures and sample
> data, all I can suggest is breaking the query down
> and reconstructing it, adding back one table at a
> time until the "duplication" appears, then correct
> that.
>
> Regards, Phill W.

This is my updated code, as soon as I join to roundgames it doubles as it's finding the game in two rows of that table.

I don't think this is possible

SELECT 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)
left JOIN `roundgames` rg ON rg.gameId = gd.gameId

WHERE `playerId` = 95 AND `didntPlay` = 0 AND t.teamType = 0 AND g.isCompleted = 1 AND YEAR(g.kickOff) = 2015