• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

Please help with a MS SQL Select query (calculate total order weight)

I have the following query:

SELECT O.OrderID
     , O.ShipFirstName
     , O.ShipLastName
     , O.ShipCompanyName
     , O.ShipAddress1
     , O.ShipAddress2
     , O.ShipCity
     , O.ShipState
     , O.ShipPostalCode
     , CT.Name AS Country
     , O.ShipPhoneNumber
     , O.ShippingMethodID
     , O.TotalShippingCost
     , (O.PaymentAmount - O.TotalShippingCost) AS FOB
     , O.OrderStatus
     , O.BatchNumber
     , O.Custom_Field_eBayID
     , C.EmailAddress
     , od_totals.Product_Count
     , od_totals.Order_Weight

FROM Orders o
JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Countries CT ON CT.Name = O.ShipCountry
INNER JOIN (
    SELECT od.OrderID
         , 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 
  AND (
      o.OrderStatus = 'PROCESSING'
      OR (
      ShippingMethodID = 6761
      AND BatchNumber IS NULL   
      AND OrderStatus <> 'CANCELLED'))
ORDER BY O.OrderID ASC
;

Open in new window


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:
     od_totals.Product_Count
     od_totals.Order_Weight

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
0
gregholl
Asked:
gregholl
4 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
remove distinct from line 27

, COUNT(od.ProductCode) AS Product_Count
0
 
Scott PletcherSenior DBACommented:
You need to sum the item qty's to get 6:

   SELECT od.OrderID
         , SUM(od.Quantity) AS Product_Count

But as long as the ProductWeight already has decimal places in it, I don't see why you're getting a wrong answer there.  You could explicitly CAST a value just to be sure:

         , SUM(CAST(od.Quantity AS decimal(9, 2)) * p.ProductWeight) AS Order_Weight
0
 
John_VidmarCommented:

1.

Remove distinct  when calculating Product_Count

2.

Order_Weight calculation looks correct, but the having-clause may be filtering out data
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi John,

How did you make these nice bullet points with numbers in it?
0
 
gplanaCommented:
Can you execute this to see what is happening?:

    SELECT od.OrderID
         , od.ProductCode
         , od.Quantity * p.ProductWeight
    FROM OrderDetails od
    INNER JOIN Products_Joined p ON p.ProductCode = od.ProductCode

and then let us know the result of this SELECT ?
0
 
greghollAuthor Commented:
Thanks guys! I got it worked out
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now