Error with Group Function, not sure why

[croman]

Ok, This code works perfectly

 $result = @mysql_query("SELECT Team, FName, LName, (100 / SUM(Att)) * SUM(CMP) AS 'pct'
        FROM Passing
                GROUP BY Fname,3 AND Lname,3
                ORDER BY 4 DESC, 1
        LIMIT 0,3"); 

However, if I try to add a WHERE statement so that it will only count players that have a total of 50 attempts or more, I get an error related to my group statement and I am not sure why

The error and the code is listed below

Error: Error performing query: Invalid use of group function

$result = @mysql_query("SELECT Team, FName, LName, (100 / SUM(Att)) * SUM(CMP) AS 'pct'
        FROM Passing WHERE SUM(Att) > 50
                GROUP BY Fname,3 AND Lname,3
                ORDER BY 4 DESC, 1
        LIMIT 0,3"); 

The only thing I added was the WHERE statement.. no idea why that would break it

Thanks

[Egghead]

Hi Croman, you certainly like your SQL queries. I can't help thinking that the problem here is in the WHERE clause (obviously!!) ...
Try using the alias in the WHERE clause?

pct.SUM(Att) > 50

I don't know why I think this will work - I have serious doubts it is any use at all, but it may get you thinking in the right direction. Good luck.

[croman]

Thanks Egghead

I am also sorry I posted it in the PHP section, I guess I got carried away with the help I get in there

[r937]

i dunno what you intended with your GROUP BY, croman, but it has to match the non-aggregate columns in the SELECT

the WHERE you wanted to use is a HAVING


SELECT Team, FName, LName
, (100 / SUM(Att)) * SUM(CMP) AS 'pct'
FROM Passing
GROUP BY Team, FName, LName
HAVING SUM(Att) > 50
ORDER BY 4 DESC, 1
LIMIT 0,3


rudy
http://rudy.ca/

[croman]

That worked Great! Thanks!