[Solved] Group by – Each Date summary value [closed]

by


You were close. You need to move the CASE...END inside the SUM() and drop TRS.DATE from the GROUP BY and ORDER BY.

SELECT TRS.ITEM,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-01' THEN TRS.QTY END) D1Q,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-02' THEN TRS.QTY END) D2Q,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-03' THEN TRS.QTY END) D3Q,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-04' THEN TRS.QTY END) D4Q,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-05' THEN TRS.QTY END) D5Q,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-06' THEN TRS.QTY END) D6Q,
    SUM(CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-07' THEN TRS.QTY END) D7Q,
    SUM(TRS.QTY) AS TOTQ,
    SUM(TRS.AMOUNT) AS TOTA
FROM TRS_TAB TRS
WHERE TRS.DATE BETWEEN '2022-01-01' AND '2022-01-07'
GROUP BY TRS.ITEM
ORDER BY TRS.ITEM

See working example at db<>fiddle.

To eliminate the “Null value is eliminated by an aggregate or other SET operation.” you could add an ELSE 0 to the case statements. You might also consider whether your really need the CAST() inCAST(TRS.DATE AS DATE).

1

solved Group by – Each Date summary value [closed]