sqldb2aggregates

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;