Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is JOIN with SUM() possible?

    I am having difficulty coming up with a valid syntax, and I don't even know if it is possible.

    I have two tables: orders and order_item

    orders has information like customer_id, date_purchased and so on.

    order_item has:

    id (key)
    orders_id
    product_id
    quantity
    unit_price
    total_price

    What I need to do is to get all (*) fields from orders, and (SUM(order_item.quantity) WHERE orders.id = order_item.orders_id) all in a single sql command.

    Basically the syntax I am currently trying to get to work is:

    Code:
    SELECT a.*, SUM(b.quantity) FROM orders AS a, order_item AS b WHERE a.company_id = 3 AND a.id = b.orders_id
    I have a feeling that I need to do a LEFT JOIN as a nested statement... Any ideas?

  • #2
    Senior Coder
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    1,315
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Code:
    SELECT a.*, SUM(b.quantity) FROM orders AS a LEFT JOIN order_item AS b ON a.id = b.orders_id WHERE a.company_id = 3
    Forget style. Code to semantics. Seperate style from structure, and structure from behaviour.
    I code to specs, and test only in Firefox (unless stated otherwise).

  • #3
    New to the CF scene
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the assistance but running that query I get the following error:

    #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

  • #4
    Senior Coder
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    1,315
    Thanks
    0
    Thanked 2 Times in 2 Posts
    That has nothing to do with the JOIN, it's because you haven't got a GROUP clause for your SUM to work with.
    Last edited by hemebond; 05-14-2005 at 05:30 AM.
    Forget style. Code to semantics. Seperate style from structure, and structure from behaviour.
    I code to specs, and test only in Firefox (unless stated otherwise).


  •  

    Posting Permissions

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