SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot croman's Avatar
    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

  2. #2
    SitePoint Zealot Egghead's Avatar
    Join Date
    Feb 2002
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    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.

  3. #3
    SitePoint Zealot croman's Avatar
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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/

  5. #5
    SitePoint Zealot croman's Avatar
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That worked Great! Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •