Group by - Each Date summary value

Question

I have values in a table like below

enter image description here

I want to check the quantity and amount of this for each day.

enter image description here

Please help.

My query is below.

SELECT TRS.ITEM,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-01' THEN SUM(TRS.QTY) END) D1Q,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-02' THEN SUM(TRS.QTY) END) D2Q,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-03' THEN SUM(TRS.QTY) END) D3Q,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-04' THEN SUM(TRS.QTY) END) D4Q,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-05' THEN SUM(TRS.QTY) END) D5Q,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-06' THEN SUM(TRS.QTY) END) D6Q,
    (CASE WHEN CAST(TRS.DATE AS DATE)='2022-01-07' THEN SUM(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, TRS.DATE
ORDER BY TRS.ITEM, TRS.DATE

Result is like below. I want just item group just one record each date.

 =========================================================
 ITM   1/1   1/2   1/3   1/4   1/5  1/6   1/7  QTY  AMOUNT
  T1     3   NULL   NULL  NULL  NULL  NULL NULL  3    4.50
  T1    NULL  2    NULL  NULL   NULL NULL NULL  2     3.00

Nope. Try again. You're much more likely to get a good answer if you use formatted text instead of screen captures for your sample data. You can edit this question in-place to fix this. — Joel Coehoorn, Feb 14 '22 at 21:12
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Feb 14 '22 at 21:13
Column 'TRS.DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Error popup. — JSK, Feb 14 '22 at 21:40

score 1 · Accepted Answer · answered Feb 14 '22 at 23:33

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

Group by - Each Date summary value

1 Answers1