如何在Count语句中分组项

Question

我正在尝试创建一个查询，该查询将返回0-3天、4-7天、8-14天和15+报告的索赔总额

Select DATEDiff(DD,LossDate,DateReported) As TimeToReport,Count(ClaimId) As Num from LossRun
where PolicyNum='1234567890'
And PolTerm='201403'
Group By  DATEDiff(DD,LossDate,DateReported)
order by DATEDiff(DD,LossDate,DateReported);

这就是我得到的

TimeToReport NumofClaims
0   5
1   3
2   1
3   4
4   3
5   2
6   2
7   2
8   1
12  1
13  1
14  2
15  2
48  1
52  1
107 1
121 1
147 1
533 1

基本上，我想看到0-3，4-7,8-14，其余的，，，timeToReport的总数。

Answer 1

您可以尝试将SUM与CASW WHEN结合使用。

select 
    SUM(CASW WHEN TimeToReport <= 3 THEN NumofClaims ELSE 0 END) '0~3 day', 
    SUM(CASW WHEN TimeToReport >= 4 AND  TimeToReport <=7 THEN NumofClaims END) '4-7 days',
    SUM(CASW WHEN TimeToReport >= 8 AND  TimeToReport <=14 THEN NumofClaims ELSE 0 END) '8-14 days',
    SUM(CASW WHEN TimeToReport >= 15 THEN NumofClaims ELSE 0 END) '15+ day'
from (
    Select DATEDiff(DD,LossDate,DateReported) As TimeToReport,Count(ClaimId) As Num 
    from LossRun
    where PolicyNum='1234567890'
    And PolTerm='201403'
    Group By  DATEDiff(DD,LossDate,DateReported)
) t

Answer 2

最简单的方法是创建自己的临时表，其中包括每个桶的最小和最大值，然后加入它。

declare @t table (OrderedID int, EmpID int, EffDate date, Salary money)

insert into @t
values
(1,1234,'20150101',1)
,(2,1234,'20160101',2)
,(3,1234,'20170101',8)
,(4,1234,'20180101',15)
,(1,2351,'20150101',17)
,(5,1234,'20190101',4)
,(5,1234,'20190101',2)
,(5,1234,'20190101',9)

declare @Bin table (MinVal int, MaxVal int)

insert into @Bin
values
(1,3)
,(4,6)
,(7,9)
,(10,15)
,(15,20)
,(20,25)



Select
B.MinVal,count(T.EmpID) as EmpsInBin
From @t T
inner join @Bin B on T.Salary between B.MinVal and B.MaxVal
group by B.MinVal

输出

MinVal  EmpsInBin
1       3
4       1
7       2
10      1
15      2