Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: Syntax check for GROUP BY

    Hi,
    I'm having trouble with this query:

    SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY)) as BaskDate, ofm_baskets.Code, Sum(ofm_baskets.Quantity) AS TotalQty
    FROM (ofm_basklist INNER JOIN ofm_basklupd ON ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
    GROUP BY ofm_baskets.Code
    HAVING (((ofm_basklist.Order_id)=0))
    ORDER BY Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY));

    I get the following error:
    ERROR 1111: Invalid use of group function

    Can someone give me an idea of what's wrong with this query? I'm using MySQLd version 4.0.16.

    Thanks,

    Eric

  2. #2
    Join Date
    Feb 2004
    Posts
    3

    Re: Syntax check for GROUP BY

    Originally posted by electroniceric
    Hi,
    I'm having trouble with this query:

    SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY)) as BaskDate, ofm_baskets.Code, Sum(ofm_baskets.Quantity) AS TotalQty
    FROM (ofm_basklist INNER JOIN ofm_basklupd ON ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
    GROUP BY ofm_baskets.Code
    HAVING (((ofm_basklist.Order_id)=0))
    ORDER BY Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY));

    I get the following error:
    ERROR 1111: Invalid use of group function

    Can someone give me an idea of what's wrong with this query? I'm using MySQLd version 4.0.16.

    Thanks,

    Eric
    May be you need use 2 group parameters?
    For example, GROUP BY ofm_baskets.Code, TotalQty

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the datatype of Lastupdate? what are some sample values in that column?

    your GROUP BY is correct assuming that the expression involving Lastupdate is correct

    rewrite the HAVING condition as a WHERE condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2003
    Posts
    3
    Aha!
    Currently Lastupdate is varchar(10), but that was not actually the source of the problem. The problem turns out to be attempting to use the grouping function Max in the ORDER BY clause.

    After changing the query to:
    SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY)) as BaskDate, ofm_baskets.Code, Sum(ofm_baskets.Quantity) AS TotalQty
    FROM (ofm_basklist INNER JOIN ofm_basklupd ON ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
    WHERE (ofm_basklist.Order_id)=0
    GROUP BY ofm_baskets.Code
    ORDER BY BaskDate;

    Now it works perfectly.

    I cut my teeth on Access/SQL SERVER queries, and my original query is how Access does ORDER BY clauses, so beware of that if you go from one to the other.

    Many thanks for the helpful suggestions.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're right, and i knew that too! sorry

    can't use the aggregate in the ORDER BY in mysql, can't use the alias in sql server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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