CODEWITHSUNDEEP
sqlleft-join
pgendron
pgendron

Reputation: 23

Multiple SUM in SQL Query including LEFT JOIN

I want to display all the work order for a specific customers with the sum of what he owes us.

My problem is that with my current query, as soon as there is more than one product ('Work_Order_Products' table), times multiplies by the number of record returned by the 'Work_Order_Products' table.

Is there a way to overcome this problem? Thanks

SELECT
    W.ID_WorkOrder AS ID_WorkOrder,
    W.Number AS Number,
    W.Date AS Date,
    C.Name AS Name,
    SUM(WOP.Quantity * WOP.Price) AS totProducts,
    SUM(WOI.Times) AS totTimes
FROM Work_Order W
LEFT JOIN Work_Order_Products WOP ON WOP.ID_WorkOrder = W.ID_WorkOrder
LEFT JOIN Work_Order_Instructions WOI ON WOI.ID_WorkOrder = W.ID_WorkOrder
LEFT JOIN Customers C ON C.ID_WorkOrder = W.ID_Customer
WHERE W.ID_Customer = x
GROUP BY
    ID_WorkOrder,
    Number,
    Date,
    Name

Upvotes: 0

Views: 86

Answers (3)

Chris Hackett
Chris Hackett

Reputation: 449

I would try a correlated subquery instead of a SUM:

replacing:

SUM(WOI.Times) AS totTimes

with:

(
    select SUM(WOI.Times) from Work_Order_Instructions WOI
    where
        WOI.ID_WorkOrder = W.ID_WorkOrder

) as totTimes

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

In order to avoid the multiplier from either the Work_Order_Products or the Work_Order_Instructions tables, handle your aggregations before you join the tables.

SELECT
  W.ID_WorkOrder AS ID_WorkOrder
 ,W.Number AS Number
 ,W.Date AS Date
 ,C.Name AS Name
 ,WOP.totProducts
 ,WOI.totTimes
FROM
  Work_Order AS W
LEFT JOIN
  (
    SELECT 
      ID_WorkOrder
      ,SUM(Quantity * Price) AS totProducts
    FROM
      Work_Order_Products
    GROUP BY
      ID_WorkOrder

  ) AS WOP
    ON
    WOP.ID_WorkOrder = W.ID_WorkOrder
LEFT JOIN
  (
    SELECT 
      ID_WorkOrder
      ,SUM(Times) AS totTimes
    FROM
      Work_Order_Instructions
    GROUP BY
      ID_WorkOrder
  ) AS WOI
    ON
    WOI.ID_WorkOrder = W.ID_WorkOrder
LEFT JOIN
  Customers AS C
    ON
    C.ID_WorkOrder = W.ID_Customer
WHERE
  W.ID_Customer = x;

Upvotes: 0

Jamie F
Jamie F

Reputation: 23789

In the line of what Chris suggested, this might work:

SELECT
    W.ID_WorkOrder AS ID_WorkOrder,
    W.Number AS Number,
    W.Date AS Date,
    C.Name AS Name,
    products.ProductCount AS totProducts,
    Instructions.totTimes AS totTimes
FROM Work_Order W
    LEFT JOIN
        (SELECT WOP.ID_WorkOrder, SUM(WOP.Quantity * WOP.Price) AS ProductCount
        FROM Work_Order_Products WOP
        GROUP BY WOP.ID_WorkOrder) AS products
    ON  products.ID_WorkOrder = W.ID_WorkOrder
    LEFT JOIN
        (SELECT WOI.ID_WorkOrder, SUM(WOI.Times) AS totTimes
        FROM Work_Order_Instructions WOI
        GROUP BY WOI.ID_WorkOrder) AS Instructions
    ON Instructions.ID_WorkOrder = W.ID_WorkOrder
    LEFT JOIN Customers C ON C.ID_WorkOrder = W.ID_Customer
WHERE W.ID_Customer = x
GROUP BY
    W.ID_WorkOrder,
    Number,
    Date,
    Name

That is, use parenthesis to make "Sub tables" which will require their own aliases. (There might be syntax errors here. I don't have the data to run this, of course.)

Upvotes: 0

Related Questions