Apply column filter on summed columns in DB2

Question

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?

score 1 · Accepted Answer · answered Aug 11 '21 at 15:14

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;

Thank you very much, this was exactly what I was looking for — bran, Aug 12 '21 at 01:36

score 0 · Answer 2 · answered Aug 11 '21 at 18:05

I made a stab at what I think you were asking to get:

   SELECT 'SLSGSV' as DMTYPE,1 as DMVL01,2 as DMVL02,3 as DMVL03,4 as DMVL04,5 as DMVL05 INTO #temptable
   DECLARE @sumvariable INT

SELECT @sumvariable = SUM(D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05) 
FROM
    #temptable D
SELECT
        CASE WHEN DMTYPE = 'SLSGSV' THEN @sumvariable END AS COST,    
        CASE WHEN DMTYPE = 'RTNCST' THEN @sumvariable END AS RTNCST,  
        CASE WHEN DMTYPE = 'RTNNET' THEN @sumvariable END AS RTNNET,  
        CASE WHEN DMTYPE = 'RTNQTY' THEN @sumvariable END AS RTNQTY,  
        CASE WHEN DMTYPE = 'RTNVAL' THEN @sumvariable END AS RTNVAL,
        CASE WHEN DMTYPE = 'SLSGSV' THEN @sumvariable END AS SLSGSV,
        CASE WHEN DMTYPE = 'SLSLST' THEN @sumvariable END AS SLSLST,
        CASE WHEN DMTYPE = 'SLSNIV' THEN @sumvariable END AS SLSNIV,
        CASE WHEN DMTYPE = 'SLSNTN' THEN @sumvariable END AS SLSNTN,
        CASE WHEN DMTYPE = 'SLSQTY' THEN @sumvariable END AS SLSQTY,
        CASE WHEN DMTYPE = 'XXD' THEN @sumvariable END AS XXD
FROM #temptable

My Understanding is that you would be setting the variable based on the year need.

Apply column filter on summed columns in DB2

2 Answers2