如何使用T-SQL按天对数据分组和求和?

Question

我有一个这样的表

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      |

我想得到这个输出

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

到目前为止，我尝试了一些分组分组的条款，但我没有成功。

下面是测试代码

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)

Answer 1

您可以使用大小写表达式来检查日期是否为该月的第一天，然后按以下方式汇总:

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

看到演示

Answer 2

据我所知，您想要"提取"从您的日期开始的年份和月份列出来，数一数。我认为你可以使用下面的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;

结果:

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

这里是提琴