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

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)

CodePudding user response：

As I understood you want to "extract" year and month from your datex column and count it. I think you can use below SQL:

with cte as(
    select 
        concat(year(datex), '-', month(datex)) as datex,
        countx
    from test
    where not datex in ( '2022-12-01' )
    )
select
    datex,
    count(1)
from cte
group by datex;

Result:

date       | count_sum  |
-------------------------
2022-12    | 3          |
2022-11    | 4          |

CodePudding user response：

You may use a case expression to check if the data is the first date 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