Left join returning bad values

Question

I'm not very good with SQL queries but I attempted to write this one:

SELECT DATEPART(YY,Orders.OrderDate) as Year,
DATEPART(MM,Orders.OrderDate) as Month,
(SUM(case when OrderDetails.ProductCode = 'XXX' then 
OrderDetails.ProductPrice else 0 end) + SUM(Orders.Total))
AS XXX 
FROM Orders 
LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 
WHERE Orders.OrderStatus = 'Shipped' 
GROUP BY DATEPART(MM,Orders.OrderDate), DATEPART(YY,Orders.OrderDate) 
ORDER BY DATEPART(YY,Orders.OrderDate),DATEPART(MM,Orders.OrderDate)

The OrderDetails is linked to the Orders table by the field OrderID. In this SELECT query I'm trying to get the SUM of OrderDetails.ProductPrice when the OrderDetails.ProductCode is XXX and add it to the Orders.Total to get total amounts for each month/year.

The query is working except for one problem (that's probably either a amateur mistake or has been worked around several times). When performing the LEFT JOIN, the OrderDetails table can have multiple records linked to the Orders table which is throwing bad results in the SUM(Orders.Total). I've isolated that issue I just can't seem to fix it.

Can anybody point me in the right direction?

Side note: using `DATEPART(...)` in the `GROUP BY`/`ORDER BY` will prevent the optimizer from using indices to fulfill that part of the query, potentially making it very slow. If you can instead create a range table that you can join to (something like what I do [in this answer](http://stackoverflow.com/questions/24156202/postgresql-group-month-wise-with-missing-values/24161958#24161958), although you'd need to use a recursive query to create the range), you should be able to do index-answerable comparisons. — Clockwork-Muse, Dec 12 '14 at 04:30

score 0 · Accepted Answer · answered Dec 12 '14 at 01:34

If we assume that the XXX product only appears at most once for each order, then this should work:

SELECT year(o.OrderDate) as Year, month(o.OrderDate) as Month,
       (COALESCE(SUM(od.ProductPrice), 0) + SUM(o.Total)) AS XXX 
FROM Orders o LEFT JOIN
     OrderDetails od
     ON o.OrderID = od.OrderID AND od.ProductCode = 'XXX'
WHERE o.OrderStatus = 'Shipped' 
GROUP BY year(o.OrderDate), month(o.OrderDate) 
ORDER BY year(o.OrderDate), month(o.OrderDate);

If it can appear multiple times, then move that part of the aggregation to a subquery:

SELECT year(o.OrderDate) as Year, month(o.OrderDate) as Month,
       (COALESCE(XXX, 0) + SUM(o.Total)) AS XXX 
FROM Orders o LEFT JOIN
     (SELECT od.OrderId, SUM(od.ProductPrice) as XXX
      FROM OrderDetails od
      WHERE od.ProductCode = 'XXX'
      GROUP BY od.OrderId
     ) od
     ON o.OrderID = od.OrderID 
WHERE o.OrderStatus = 'Shipped' 
GROUP BY year(o.OrderDate), month(o.OrderDate) 
ORDER BY year(o.OrderDate), month(o.OrderDate);

Left join returning bad values

1 Answers1