Combining Values in a Column by Date

I have been tasked with writing this simple query and am not sure where to go from here. I am trying to create a report that shows 3 columns: Date_Column, Id_Column, & Amount_Col.

The table for Id_Column has different IDs based on a location column (in the table, but not in my query below). For the report, certain IDs need to be grouped together. With the below query, it displays the IDs correctly in the same column, but I need to total them by date. As currently written, it will display multiple listings for the grouped ID’s per date instead of totalling them.

SELECT Date_Column,
    CASE WHEN Id_Column in (11, 21) THEN '11, 21'
    ELSE CAST(Id_Column AS VARCHAR(255))
    END AS Id_Column,
    FORMAT(SUM(Amount_Col), 'C') AS Amount_Col
FROM table
GROUP BY Id_Column, Date_Column
ORDER BY Id_Column, Date_Column;

What I get:

Date_Column	Id_Column	Amount_Column
2021-04-1	11, 21	$10
2021-04-1	11, 21	$10
2021-04-3	11, 21	$5

What I need:

Date_Column	Id_Column	Amount_Col
2021-04-1	11, 21	$20
2021-04-3	11, 21	$5

Appreciate any help. Thanks!

Answer

I suspect that you need to aggregate by the result of the case expression:

SELECT Date_Column,
       (CASE WHEN Id_Column in (11, 21) THEN '11, 21'
             ELSE CAST(Id_Column AS VARCHAR(255))
        END) AS Id_Column,
       FORMAT(SUM(Amount_Col), 'C') AS Amount_Col
FROM table
GROUP BY (CASE WHEN Id_Column in (11, 21) THEN '11, 21'
               ELSE CAST(Id_Column AS VARCHAR(255))
          END), Date_Column
ORDER BY Date_Column;

If you want the SUM() just for the date, I would suggest leaving the id out of the query altogether.