SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict streetlife's Avatar
    Join Date
    Mar 2004
    Location
    D-Town
    Posts
    369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    JOIN and GROUP BY

    Basically, I am having problems using SUM() with multiple JOINs and GROUP BY. The values returned for the SUM() are the same for each row, which tells me that the values are being added together before the GROUP BY, not after. I think that ideally I would be able to do a GROUP BY for each JOIN. Hopefully someone can tell me how to do this in the one query. Thanks

    Code:
    SELECT purOrderItems.invItemFrame_id, invItemFrames.invItemFrame_name, purOrderItems.purOrderItem_unitCost,
    SUM( purOrderItems.purOrderItem_quantity ) AS quantity,
    IFNULL( SUM( purOrderReceivedItems.purOrderReceivedItem_quantity ) , 0 ) AS received,
    IFNULL(SUM(purOrderCancelledItems.purOrderCancelledItem_quantity),0) AS cancelled,
    FROM purOrders, invItemFrames
    LEFT JOIN purOrderItems ON purOrders.purOrder_id = purOrderItems.purOrder_id
    LEFT JOIN purOrderReceived ON purOrders.purOrder_id = purOrderReceived.purOrder_id
    LEFT JOIN purOrderReceivedItems ON purOrderReceived.purOrderReceived_id = purOrderReceivedItems.purOrderReceived_id
    LEFT JOIN purOrderCancelled ON purOrders.purOrder_id = purOrderCancelled.purOrder_id
    LEFT JOIN purOrderCancelledItems ON purOrderCancelled.purOrderCancelled_id = purOrderCancelledItems.purOrderCancelled_id
    WHERE invItemFrames.invItemFrame_id = purOrderItems.invItemFrame_id AND purOrders.purOrder_id = '91'
    GROUP BY invItemFrame_id
    ORDER BY invItemFrames.invItemFrame_name

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    GROUP
    BY purOrderItems.invItemFrame_id
    , invItemFrames.invItemFrame_name
    , purOrderItems.purOrderItem_unitCost
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict streetlife's Avatar
    Join Date
    Mar 2004
    Location
    D-Town
    Posts
    369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    GROUP BY purOrderItems.invItemFrame_id, invItemFrames.invItemFrame_name, purOrderItems.purOrderItem_unitCost
    invItemFrame_id invItemFrame_name purOrderItem_unitCost

    Thanks for your reply, but that doesn't appear to affect anything. Some of the quantity are still set to either 75,150, or 300, all the received values are at 81, and the cancelled are at 0 because there are no cancelled values. Any ideas?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, it must be because the relationships that you
    include in the query involve unrelated rows
    (unrelated to each other, even though related to purOrders)

    you are getting cross join effects

    unlesds you're on 4.1 which supports subqueries,
    try breaking the query apart into two or more,
    each of which concentrates on its own sums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict streetlife's Avatar
    Join Date
    Mar 2004
    Location
    D-Town
    Posts
    369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I just checked with my administartor and he's not gonna upgrade to 4.1 cos it's still in alpha, it was hard enough just to get him to upgrade to 4 a couple of months back!
    So should i create some temporary tables with each query and then join them together, or just do the joining in PHP? Or do you have a better suggestion?

    Again, thank you so much for your help.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    since you are only querying one PO,
    i would combine the results of the
    several queries in my scripting language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict streetlife's Avatar
    Join Date
    Mar 2004
    Location
    D-Town
    Posts
    369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks so much!


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
  •