Need help with WHERE statement

[Phailak]

Hail,

I have this query I'm building for my hockey league website, so far I'm getting prety much what I want but for some reason I can't narrow it down. Basically, the query gives me the stats of the goalies in the league, in this case some of the goalies returned have 0 games played (because they played in previous seasons but not current one) so I simply want to add to my WHERE statement GP > 0 (I also tried the long version of sum(if (ResultQuery.result="W", 1, 0))+sum(if (ResultQuery.result="L", 1, 0))+sum(if (ResultQuery.result="OTL", 1, 0))+sum(if (ResultQuery.result="T", 1, 0)) > 0) which doesn't work. It sounds like a simple thing I'm missing so I'm hoping someone here can point it out (the WHERE I'm targetting is in red, I want only records which are > than 0):

SELECT gamesplayed.PlayerID, players.PlayerName,
[!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]sum(if (ResultQuery.result="W", 1, 0))+sum(if (ResultQuery.result="L", 1, 0))+sum(if (ResultQuery.result="OTL", 1, 0))+sum(if (ResultQuery.result="T", 1, 0)) as GP[!--colorc--][/span][!--/colorc--],
sum(if (ResultQuery.result="W", 1, 0)) as Wins,
sum(if (ResultQuery.result="L", 1, 0)) as Losses,
sum(if (ResultQuery.result="OTL", 1, 0)) as OTL,
sum(if (ResultQuery.result="T", 1, 0)) as Ties,
sum(ResultQuery.TGA) as GA
FROM gamesplayed INNER JOIN players ON gamesplayed.PlayerID = players.PlayerID
LEFT JOIN (SELECT calendar.CalendarID,
if(teams.TeamID=calendar.HomeTeam, calendar.HomeTeam, calendar.AwayTeam) as Team,
if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals) as TeamGoals,
if(teams.TeamID=calendar.AwayTeam, calendar.HomeTeam, calendar.AwayTeam) as OppTeam,
if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) as OppTeamGoals,
(if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) - if(isnull(OT.OTgoals),0,OT.OTgoals)) as TGA,
OT.OT,
if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)>if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals),"W",if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)=if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals),"T",if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)<if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) AND OT.OT=1, "OTL","L"))) as Result
FROM teams INNER JOIN calendar ON teams.TeamID = calendar.HomeTeam OR teams.TeamID = calendar.AwayTeam
LEFT JOIN (SELECT goals.GameID, goals.TeamID, count(goals.GameID) as Goals FROM goals GROUP BY goals.GameID, goals.TeamID)
AS HomeGoalQuery ON calendar.CalendarID = HomeGoalQuery.GameID AND teams.TeamID= HomeGoalQuery.TeamID
LEFT JOIN (SELECT goals.GameID, goals.OppTeamID, count(goals.GameID) as Goals FROM goals GROUP BY goals.GameID, goals.TeamID)
AS AwayGoalQuery ON calendar.CalendarID = AwayGoalQuery.GameID AND teams.TeamID = AwayGoalQuery.OppTeamID
LEFT JOIN (SELECT calendar.CalendarID, count(calendar.CalendarID) as OTgoals, '1' as OT FROM calendar
INNER JOIN goals on calendar.CalendarID = goals.GameID WHERE Period = 4 GROUP BY calendar.CalendarID) as OT ON calendar.CalendarID = OT.CalendarID
WHERE calendar.Season = 3 AND calendar.Playoffs = 0 AND Cancellation = 0 AND teams.TeamID <> 17 AND teams.TeamID <> 18 ORDER BY teams.TeamID, calendar.CalendarID) AS ResultQuery ON gamesplayed.GameID = ResultQuery.CalendarID AND gamesplayed.TeamID = ResultQuery.Team
WHERE players.Position = "G"
GROUP BY gamesplayed.PlayerID

[shoz]

[code]
WHERE players.Position = "G"
GROUP BY gamesplayed.PlayerID
HAVING GP > 0
[/code]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]SELECT SYNTAX[/a] (Do a "find" throughout the text for instances of "HAVING")

[Phailak]

[!--quoteo(post=374316:date=May 16 2006, 12:03 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 16 2006, 12:03 PM) [snapback]374316[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
WHERE players.Position = "G"
GROUP BY gamesplayed.PlayerID
HAVING GP > 0
[/code]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]SELECT SYNTAX[/a] (Do a "find" throughout the text for instances of "HAVING")
[/quote]

Yep, that worked, thanks!

[fenway]

I don't even want to guess how slow that query might be... btw, you should be using IFNULL(), instead of and if() with an isnull() inside. It's cleaner, easier and read, and the parser doesn't have to guess.

e.g.

[code]ifnull(HomeGoalQuery.Goals,0) [/code]
instead of:

[code]if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals) [/code]

[Phailak]

[!--quoteo(post=374406:date=May 16 2006, 03:53 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 16 2006, 03:53 PM) [snapback]374406[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I don't even want to guess how slow that query might be... btw, you should be using IFNULL(), instead of and if() with an isnull() inside. It's cleaner, easier and read, and the parser doesn't have to guess.

e.g.

[code]ifnull(HomeGoalQuery.Goals,0) [/code]
instead of:

[code]if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals) [/code]
[/quote]

Oh, well this query is from trial and error on my part so it might look very complicated for no reason, thanks for the suggestion, although the query itself is quite fast, considering the amount of data is pretty low, I guess I don't see the effects of any slowdowns. Still anything I can do to make it clearer is appreciated