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

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()` in`CAST(TRS.DATE AS DATE)`.

solved Group by – Each Date summary value [closed]