SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Florida
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Invalid use of group function Aaagh!!

    Hi,

    I got stuck on this query:

    SELECT xcart_products.productid
    FROM (xcart_order_details INNER JOIN xcart_orders ON xcart_order_details.orderid = xcart_orders.orderid) INNER JOIN xcart_products ON xcart_order_details.productid = xcart_products.productid
    GROUP BY xcart_products.productid
    ORDER BY Sum(xcart_order_details.amount);

    SQL just keep saying: INVALID SQL: 1111 : Invalid use of group function Does anybody know what's wrong with the query??

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    When you use a group by, it's implying some other sort of aggregate functionality (sum, count, min, max, etc). In other words, you need something like this:

    SELECT p.productid, SUM(d.amount)
    FROM xcart_order_details d
    INNER JOIN xcart_orders o ON d.orderid = o.orderid
    INNER JOIN xcart_products p ON d.productid = p.productid
    GROUP BY p.productid
    ORDER BY Sum(xcart_order_details.amount);
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    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)
    Quote Originally Posted by jiser View Post
    Does anybody know what's wrong with the query??
    it looks okay to me

    are you sure that's the exact query? what database system is this?

    do you get the same error without the ORDER BY?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Rudy - it looks to be a mySQL error.


    jiser, you may want to refer to this thread since it seems to be very close to your situation.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


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
  •