Post reply

Grouping to Total

  • July 16, 2017 at 3:21 pm

    #327377

    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

    #1951097

    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

    #1951102

    SUM(TCost + OffCost) OVER (PARTITION BY [Dept#])

    John

  • July 17, 2017 at 6:07 am

    #1951119

    This should help
    SELECT [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#]

    Kingston Dhasian

    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