I have the following query:
, CT.Name AS Country
, (O.PaymentAmount - O.TotalShippingCost) AS FOB
FROM Orders o
JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Countries CT ON CT.Name = O.ShipCountry
INNER JOIN (
, COUNT(DISTINCT od.ProductCode) AS Product_Count
, SUM(od.Quantity * p.ProductWeight) AS Order_Weight
FROM OrderDetails od
INNER JOIN Products_Joined p ON p.ProductCode = od.ProductCode
GROUP BY od.OrderID
HAVING MAX(CASE WHEN od.QtyOnBackOrder > 0 THEN 1 ELSE 0 END) = 0
) AS od_totals ON od_totals.OrderID = o.OrderID
WHERE o.ShipDate IS NULL
AND o.BatchNumber IS NULL
o.OrderStatus = 'PROCESSING'
ShippingMethodID = 6761
AND BatchNumber IS NULL
AND OrderStatus <> 'CANCELLED'))
ORDER BY O.OrderID ASC
The problem seems to be in lines 27 and 28. I've done something wrong here.
You'll notice that there are some calculated fields, included these two:
The query is NOT returning the proper results.
For example, say the order consists of 3 Widgets:
3 x WidgetA (weighing 0.5 kg each)
2 x WidgetB (weighing 0.25 kg each)
1 x WidgetC (weight 0.25 kg)
What I would LIKE to see in my query result is
od_totals.Product_Count = 6 (i.e. 3 + 2 + 1)
od_totals.Order_Weight = 2.25 kg (i.e. (3x0.5kg) + (2x0.25kg) + (1x0.25kg)
I hope you're "with me" so far.
However, this is what the query is returning:
od_totals.Product_Count = 3
od_totals.Order_Weight = 1.0 kg
So what have I done wrong. Can somebody please help me redo the query to return the correct results?
Thanks guys! :D