henryaaron henryaaron -4 years ago 59
SQL Question

Left join returning bad values

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?

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download