SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
May 13, 2003, 09:04 #1
- Join Date
- Apr 2003
- Location
- Minneapolis, MN
- Posts
- 157
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Error with Group Function, not sure why
Ok, This code works perfectly
PHP Code:$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
PHP Code:$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
-
May 13, 2003, 12:06 #2
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?
Code:pct.SUM(Att) > 50
-
May 13, 2003, 12:31 #3
- Join Date
- Apr 2003
- Location
- Minneapolis, MN
- Posts
- 157
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 13, 2003, 13:12 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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/
-
May 14, 2003, 06:25 #5
- Join Date
- Apr 2003
- Location
- Minneapolis, MN
- Posts
- 157
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
That worked Great! Thanks!
Bookmarks