sql - 如何按日期创建 SQL 多次计数计数查询？

Question

我有一个按部门选择计数组的查询。

select department, count(*) group by department

这将返回两列数据结果。我想知道是否选择 cout，按今天计数和按本月计数相同的查询。

Department  Total  Today  ThisMonth
AAA         40     8      23
BBB         20     6      11

Answer 1

试试这个

select department,count(*),
sum(case when datecolumn= GETDATE() then 1 else 0 end) as Today,
sum(case when month(datecolumn) =month(GETDATE() and year(datecolumn) =year(GETDATE()) then 1 else 0 end) as ThisMonth
from YourTable
group by department