How can I group and sum data by day using T-SQL?

December 28, 2022

I have a table like this

datex      | countx |
---------------------
2022-12-04 | 1      |
2022-12-03 | 2      |
2022-12-02 | 1      |
2022-12-01 | 3      |
2022-11-30 | 1      |
2022-11-29 | 1      |
2022-11-28 | 1      |
2022-11-27 | 2      |

I want to get this output

datex      | count_sum  |
-------------------------
2022-12    | 4          |
2022-12-01 | 3          |
2022-11    | 5          |

So far I tried some group by clause but I didn’t succeed.

Here is test code

declare @test table  
(
    datex date,
    countx int
)

insert into @test 
values ('2022-12-04', 1),
       ('2022-12-03', 2),
       ('2022-12-02', 1),
       ('2022-12-01', 3),
       ('2022-11-30', 1),
       ('2022-11-29', 1),
       ('2022-11-28', 1),
       ('2022-11-27', 2)

>Solution :

You may use a case expression to check if the date is the first day of the month then aggregate as the following:

with check_date as
(
  select case
          when Day([date])=1
           Then Cast([date] as varchar(10))
           else Format([date], 'yyyy-MM')
          end As dt,
         [count]
  from table_name
)
select dt, sum([count]) as count_sum  
from check_date
group by dt
order by dt desc

See demo