July 16, 2017 at 3:21 pm
Dept# TransCode TCost OfficeCode OffCost Totalcost
7777 wetwear 6.00 0 0
7777 wetwear 16.52 0 0
7777 0 Sportx 106.00 0
7777 0 Sportz 46.00 0
8888 wetwear 45.00 Null 0 0
8888 wetwear 1016.00 Null 0 0
9999 wetwear 0.00 Null 0 0
9999 0.00 Null 0 0
9999 0.00 Null 0 0
I would like the "TotalCost" be the sum of TCost + OffCost for each Dept#, so for Dept# 7777 it will be:
7777 wetwear 32.52 Sportz 152 174.52
July 17, 2017 at 3:14 am
Please supply a valid Sample table with DDL and DLM. The example you have has shows 6 columns, however, several of your rows only have 5, and varying data types across them. For example, Transcode looks to have varchar (wetwear), int (0) and decimal (0.00) datatypes. You've been using SSC long enough to know to do this. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 17, 2017 at 3:51 am
SUM(TCost + OffCost) OVER (PARTITION BY [Dept#])
John
July 17, 2017 at 6:07 am
This should helpSELECT [Dept#],
MAX( CASE WHEN TransCode = 'wetwear' THEN 'wetwear' ELSE NULL END ) ColName1,
SUM( CASE WHEN TransCode = 'wetwear' THEN TCost + OffCost ELSE NULL END ) ColValue1,
MAX( CASE WHEN TransCode = 'Sportz' THEN 'Sportz' ELSE NULL END ) ColName2,
SUM( CASE WHEN TransCode = 'Sportz' THEN TCost + OffCost ELSE NULL END ) ColValue2,
SUM( TCost + OffCost ) ColValue3
FROM TableName
GROUP BY [Dept#]
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply