[SOLVED] Apply column filter on summed columns in DB2

Apply column filter on summed columns in DB2

I have the following DB2 table:

COLUMN_NAME	DATA_TYPE	TYPE_NAME	COLUMN_SIZE	COLUMN_TEXT
DMPROD	-2	CHAR () FOR BIT DATA	35	Product Code
DMPTYP	-2	CHAR () FOR BIT DATA	1	Period Type
DMTYPE	-2	CHAR () FOR BIT DATA	6	Type of Data
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
DMYEAR	3	DECIMAL	4	Fiscal Year

Below query would return the sum of value periods for each DMTYP:

   SELECT
    D.DMPROD,
    Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS COST,    
    Sum(CASE WHEN DMTYPE = 'RTNCST' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNCST,  
    Sum(CASE WHEN DMTYPE = 'RTNNET' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNNET,  
    Sum(CASE WHEN DMTYPE = 'RTNQTY' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNQTY,  
    Sum(CASE WHEN DMTYPE = 'RTNVAL' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS RTNVAL,
    Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSGSV,
    Sum(CASE WHEN DMTYPE = 'SLSLST' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSLST,
    Sum(CASE WHEN DMTYPE = 'SLSNIV' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSNIV,
    Sum(CASE WHEN DMTYPE = 'SLSNTN' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSNTN,
    Sum(CASE WHEN DMTYPE = 'SLSQTY' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS SLSQTY,
    Sum(CASE WHEN DMTYPE = 'XXD' THEN D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06 + D.DMVL07 + D.DMVL08 + D.DMVL09 + D.DMVL10 + D.DMVL11 + D.DMVL12 END) AS XXD
FROM
    DWM D
WHERE
    D.DMYEAR IN (2022)
    AND D.DMPTYP = 'M'
GROUP BY
    D.DMPROD
ORDER BY
    1;

Output is what I want, except value periods cannot easily be changed in this query without rewriting all sums. For example, say I only want to see Value Period 1-5 in DMYEAR 2022, I would need to change every case statement. Would there be an easier way, perhaps with subqueries, to keep the SUM statements the same but select only the desired value periods for the final output?

Solution

You could solve this using a subquery:

SELECT
    D.DMPROD,
    Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN val END) AS COST,    
    Sum(CASE WHEN DMTYPE = 'RTNCST' THEN val END) AS RTNCST,  
    Sum(CASE WHEN DMTYPE = 'RTNNET' THEN val END) AS RTNNET,  
    Sum(CASE WHEN DMTYPE = 'RTNQTY' THEN val END) AS RTNQTY,  
    Sum(CASE WHEN DMTYPE = 'RTNVAL' THEN val END) AS RTNVAL,
    Sum(CASE WHEN DMTYPE = 'SLSGSV' THEN val END) AS SLSGSV,
    Sum(CASE WHEN DMTYPE = 'SLSLST' THEN val END) AS SLSLST,
    Sum(CASE WHEN DMTYPE = 'SLSNIV' THEN val END) AS SLSNIV,
    Sum(CASE WHEN DMTYPE = 'SLSNTN' THEN val END) AS SLSNTN,
    Sum(CASE WHEN DMTYPE = 'SLSQTY' THEN val END) AS SLSQTY,
    Sum(CASE WHEN DMTYPE = 'XXD' THEN val END) AS XXD
FROM (SELECT D.*,
             (D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05) as val
      FROM DWM D
     ) D
WHERE D.DMYEAR IN (2022) AND D.DMPTYP = 'M'
GROUP BY D.DMPROD
ORDER BY 1;