sqlsumdb2aggregates

Receiving different output for column SUMs in DB2


I have the following table in DB2:

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 Period Type, grouping them into Product Code

SELECT
    D.DMPROD,
    Sum(D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06) AS Total
FROM
        DWM D
WHERE
        D.DMYEAR IN (2022)
    AND D.DMPTYP = 'M'
    AND D.DMTYPE IN ('RTNQTY','SLSQTY')
GROUP BY
        D.DMPROD
ORDER BY
    1;

Initial output:

DMPROD TOTAL
11105 1145.75000
11350 625.37400
13135 2270.50000
13282 -0.27500
13344 -1.03300
15105 784

Then I changed this to work with a subquery thanks to feedback from other members. This is the preferred format:

SELECT
    D.DMPROD,
    (SLSQTY + RTNQTY) AS Total
FROM
    (
    SELECT
        D.DMPROD,
        Sum(CASE WHEN D.DMTYPE = 'RTNQTY' THEN Total END) AS RTNQTY,
        Sum(CASE WHEN D.DMTYPE = 'SLSQTY' THEN Total END) AS SLSQTY
    FROM
        (
        SELECT
            D.*,
            (D.DMVL01 + D.DMVL02 + D.DMVL03 + D.DMVL04 + D.DMVL05 + D.DMVL06) AS Total
        FROM
            DWM AS D
    ) AS D
    WHERE
        D.DMYEAR IN (2022)
        AND D.DMPTYP = 'M'
    GROUP BY
        D.DMPROD
) AS D
ORDER BY 1;

but notice the sums are not the same (the initial output was correct):

DMPROD TOTAL
11105 1145.75000
11350 625.37400
13135 2270.50000
13282
13344
15105

Where did I make the mistake with the sums?


Solution

  • These expressions:

        Sum(CASE WHEN D.DMTYPE = 'RTNQTY' THEN Total END) AS RTNQTY,
        Sum(CASE WHEN D.DMTYPE = 'SLSQTY' THEN Total END) AS SLSQTY
    

    can return NULL if no rows for the DMTYPE are in the result. That, in turn, affects this calculation:

    (SLSQTY + RTNQTY) AS Total
    

    The simplest solution is to add ELSE 0:

        Sum(CASE WHEN D.DMTYPE = 'RTNQTY' THEN Total ELSE 0 END) AS RTNQTY,
        Sum(CASE WHEN D.DMTYPE = 'SLSQTY' THEN Total ELSE 0 END) AS SLSQTY
    

    This avoids the NULL value, so the total calculation does not turn into NULL.