sqldb2db2-400aggregates

Adding Multiple SUM(CASE)s to Same Column

I have the following table in DB2:

COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE COLUMN_TEXT
DMYEAR 3 DECIMAL 4 Fiscal Year
DMYTYP -2 CHAR () FOR BIT DATA 1 Year Type
DMPTYP -2 CHAR () FOR BIT DATA 1 Period Type
DMTYPE -2 CHAR () FOR BIT DATA 6 Type of Data
DMPROD -2 CHAR () FOR BIT DATA 35 Product Code
DMVL01 3 DECIMAL 17 Value Period 1
DMVL02 3 DECIMAL 17 Value Period 2
DMVL03 3 DECIMAL 17 Value Period 3
DMVL04 3 DECIMAL 17 Value Period 4
DMVL05 3 DECIMAL 17 Value Period 5
DMVL06 3 DECIMAL 17 Value Period 6
DMVL07 3 DECIMAL 17 Value Period 7
DMVL08 3 DECIMAL 17 Value Period 8
DMVL09 3 DECIMAL 17 Value Period 9
DMVL10 3 DECIMAL 17 Value Period 10
DMVL11 3 DECIMAL 17 Value Period 11
DMVL12 3 DECIMAL 17 Value Period 12

The value periods are Jan-Feb and can sum up various data types for each month like below:

SELECT 
DMYEAR,
DMPTYP,
DMPROD,
Sum(CASE WHEN DMTYPE = 'COST' THEN DMVL01 ELSE 0 END) AS COST,
Sum(CASE WHEN DMTYPE = 'COST' THEN DMVL02 ELSE 0 END) AS COST,
Sum(CASE WHEN DMTYPE = 'COST' THEN DMVL03 ELSE 0 END) AS COST,
Sum(CASE WHEN DMTYPE = 'RTNCST' THEN DMVL01 ELSE 0 END) AS RTNCST,
Sum(CASE WHEN DMTYPE = 'RTNCST' THEN DMVL02 ELSE 0 END) AS RTNCST,
Sum(CASE WHEN DMTYPE = 'RTNCST' THEN DMVL03 ELSE 0 END) AS RTNCST
FROM DWM
WHERE DMPTYP = 'M' 
GROUP BY DMYEAR, DMPTYP, DMPROD
ORDER BY 1, 3

Which produces output like this:

DMYEAR DMPTYP DMPROD COST COST COST RTNCST RTNCST RTNCST
2010 M 11105 435798.00000 441252.00000 479774.00000 -2921.00000 0.00000 0.00000
2010 M 11350 538938.00000 593764.00000 511088.00000 -4074.00000 -2503.00000 0.00000
2010 M 11364 2905557.00000 3167448.00000 3534924.00000 -5691.00000 0.00000 0.00000
2010 M 11374 854274.00000 829392.00000

But I would like to create a new column for month denoting 1-12 and adjusting the sum to produce the corresponding values so it looks like this:

DMYEAR DMMONTH DMPTYP DMPROD COST RTNCST
2011 1 M 11105 278021.00000 -7522.00000
2011 2 M 11105 615232.00000 -938.00000
2011 3 M 11105 2153272.00000 -2805.00000

I think it is possible with subqueries, but keeping in mind I have 8-9 different DMTYPEs, so I was hoping there is a more elegant solution than to make 9 case statements per DMTYPE.

Solution

  • Try this adding the corresponding expressions to the inner CASE to get all 12 (x, DMVLx) pairs:

    WITH 
  T (DMMONTH) AS (VALUES 1 UNION ALL SELECT DMMONTH + 1 FROM T WHERE DMMONTH < 12)
SELECT 
  DMYEAR, DMMONTH, DMPTYP, DMPROD
, Sum (CASE WHEN DMTYPE = 'COST'   THEN CASE DMMONTH WHEN 1 THEN DMVL01 WHEN 2 THEN DMVL02 ... WHEN 12 THEN DMVL12 END ELSE 0 END) AS COST
, Sum (CASE WHEN DMTYPE = 'RTNCST' THEN CASE DMMONTH WHEN 1 THEN DMVL01 WHEN 2 THEN DMVL02 ... WHEN 12 THEN DMVL12 END ELSE 0 END) AS RTNCST
FROM DWM, T
WHERE DMPTYP = 'M' 
GROUP BY DMYEAR, DMMONTH, DMPTYP, DMPROD
ORDER BY 1, 3