question

Noonies avatar image
Noonies asked

SUM columns based on column having the same value

I'm going to show the result data I have then explain what I am looking for. I think it may be easier to explain this way. Date Customer Broker Weight Pallet Count Total Shipping Cost Load Value Cost Per Pound Cost per Pallet Value per Pound Value per Pallet 12/7/2010 Jackson High St Logistics, Inc. 41060 24 495 31100.5 0.01 20.63 0.76 1295.85 12/7/2010 BOBBY Western Trans Logistics, LLC 28600 18 4400 23629.75 0.15 244.44 0.83 1312.76 ****12/7/2010 SJ Bev** High St Logistics, Inc. 16104 9 1200 14600 0.07133.33 0.91 1622.22 **12/7/2010 Distribute Inc** High St Logistics, Inc. 6930 5 1200 5561 0.17 240 0.8 1112.2 **12/7/2010 Garfield Inc** High St Logistics, Inc. 13956 9 1200 13883.75 0.09 133.33 0.99 1542.64** 12/7/2010 Associates Assoc Western Trans Logistics, LLC 26300 13 6475 23396 0.25 498.08 0.89 1799.69 12/7/2010 Union State Western Trans Logistics, LLC 17180 11 6475 16519 0.38 588.64 0.96 1501.73 The items in bold need to have their weight summed based on them having the same ticketnumber, which is not show in the return set in order for me to calculate the cost per pound and value per pallet accurately. I want to have the sum of weight calculated in the subqery so it can be a hidden column called TotalFreightWeight which in the outside query I will modify the weight formulas. Here is the SQL i'm working with: SELECT pvt.Date,pvt.recname, pvt.Broker ,CASE WHEN pvt.Weight IS NULL THEN 0.00 ELSE pvt.Weight END AS Weight ,CASE WHEN [1068] IS NULL THEN 0 ELSE [1068] END AS [Pallet Count] ,pvt.[Total Shipping Cost], pvt.TotalSale as [Load Value] ,CASE WHEN ROUND((pvt.[Total Shipping Cost] / NULLIF(pvt.Weight,0)),2) IS NULL THEN 0 ELSE ROUND((pvt.[Total Shipping Cost] / pvt.Weight ), 2) END AS [Cost Per Pound] ,CASE WHEN ROUND((pvt.[Total Shipping Cost] /NULLIF([1068],0)),2) IS NULL THEN 0 ELSE ROUND((pvt.[Total Shipping Cost] /[1068]),2) END as [Cost per Pallet] ,CASE WHEN ROUND((pvt.totalsale/NULLIF(pvt.Weight,0)),2) IS NULL THEN 0 ELSE ROUND((pvt.totalsale/pvt.Weight),2) END AS [Value per Pound] ,CASE WHEN ROUND((pvt.totalsale/[1068]),2) IS NULL THEN 0 ELSE ROUND((pvt.totalsale/[1068]),2) END AS [Value per Pallet] FROM (SELECT CONVERT(date, o.shippeddate, 102) as Date , customers.RecName as recname , ol.itemnid as ItemNid , o.TotalGrosslbs AS Weight ,s.RecName AS Broker ,CASE WHEN p.TotalPurchase IS NULL THEN 0 ELSE p.TotalPurchase END AS [Total Shipping Cost] ,o.TotalSale, ol.QtyShipped ,CASE WHEN (p.totalpurchase/o.TotalGrosslbs) IS NULL THEN 0 ELSE ROUND((p.totalpurchase/o.TotalGrosslbs),2) END AS [Cost per Pound] FROM (((( Orders o INNER JOIN Customers "Customers" ON o."ToCusNid"="Customers"."CusNid") INNER JOIN OrderLines ol ON ol.OrderTicketNumber = o.TicketNumber ) LEFT OUTER JOIN purchases p ON p.ponum = o.PackNote) INNER JOIN suppliers s ON s.supnid = p.FromSupNid) WHERE CONVERT(Date, o.ShippedDate, 102) >= '2010-12-07' AND CONVERT(Date, o.ShippedDate, 102) ]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]] query
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Noonies: Edit your original question, alternatively create a new answer to it. There you can mark all your T-SQL-code and click the **101010** button. That will create a code block where the statements shouldn't be escaped as links.
1 Like 1 ·
Noonies avatar image Noonies commented ·
I apologize the formatting above. I'm now sure how to format columns and all that in here.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
thanks for the query and the result set - have you got table definitions and example data to go with it - it will make the job of answering your question a lot easier
0 Likes 0 ·
Noonies avatar image Noonies commented ·
Do you need the table definitions for all the tables I reference in the query and INSERT info for the values? Sorry kind of new here. :)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Enough to be able to reproduce your scenario - so yes tables and inserts would be good. You can simplify it if you want, remove columns that don't matter, and sanitize it if you don't want to post *real* data. It just makes it easier for us to get to a starting point, without having to reverse engineer your results through the non-trivial query. More people are likely to pick it up and have a go if there's some code that sets up the base data. Don't worry about being new - I'm sure you'll find us a friendly lot!
0 Likes 0 ·
Show more comments

1 Answer

·
Noonies avatar image
Noonies answered
I resolved this by putting an outside LEFT JOIN after the pvt alias on the SUM of qtyshipped. This is resolved! Thank you for the responses. :)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.