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?
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
.