CODEWITHSUNDEEP
sql
Jim
Jim

Reputation: 27

Combining rows into 1 and totalling the amounts

I have written the below script but unfortunately have become stuck. Currently it reads

SELECT dim1        AS Costc,
       period      AS Period,
       sum(actual) AS Beds,
       CASE
         WHEN measure LIKE 'beds%' THEN ISNULL(sum(actual), 0)
       END         AS BEDS,
       CASE
         WHEN measure LIKE 'occu%' THEN ISNULL (sum(actual), 0)
       END         AS OCCU,
       measure
FROM   statistics
WHERE  measure IN (SELECT measure
                   FROM   statistics
                   WHERE  measure LIKE 'beds%'
                           OR measure LIKE 'occu%')
       AND dim1 = 'ABC'
       AND period = '201301'
GROUP  BY period,
          dim1,
          measure

which returns 

COSTC | Period | Beds | BEDS | OCCU | Measure
 ABC  | 201301 |  40  |  40  | NULL | beds_abc
 ABC  | 201301 |  35  |  35  | NULL | beds_def
 ABC  | 201301 |  30  | NULL |  30  | occu_abc
 ABC  | 201301 |  20  | NULL |  20  | occu_def

My desired output is

COSTC | Period | BEDS | OCCU
 ABC  | 201301 |  75  |  50

Any help would be appreciated Thanks

Upvotes: 1

Views: 39

Answers (1)

Martin Smith
Martin Smith

Reputation: 452977

This should do it.

Remove the measure from the GROUP BY and you need to move the aggregates outside the CASE expressions

SELECT dim1                AS Costc,
       period              AS Period,
       sum(actual)         AS Beds,
       isnull(SUM(CASE
                    WHEN measure LIKE 'beds%' THEN actual
                  END), 0) AS BEDS,
       isnull(SUM(CASE
                    WHEN measure LIKE 'occu%' THEN actual
                  END), 0) AS OCCU
FROM   statistics
WHERE  measure IN (SELECT measure
                   FROM   cukstats
                   WHERE  measure LIKE 'beds%'
                           OR measure LIKE 'occu%')
       AND dim1 = 'ABC'
       AND period = '201301'
GROUP  BY period,
          dim1

Upvotes: 1

Related Questions