gpt4 book ai didi

SQL Server Case 语句和聚合函数

转载 作者:行者123 更新时间:2023-12-02 09:36:48 25 4
gpt4 key购买 nike

我有以下表格数据:

SELECT [Quote ID], [Deductible Plan], [Age ID], [Number of Members] 
FROM finalResult_001

1381 $750 Deductible Plan Age 65 10
1381 $750 Deductible Plan Age 85+ 10
1371 $150 Deductible Plan Age 65 10
1371 $150 Deductible Plan Age 85+ 10

我正在寻找以下结果:

Quote ID Deductible Plan       Age 65      Age 85+
1381 $750 Deductible Plan 10 10
1371 $150 Deductible Plan 10 10

我想按报价 ID 和免赔额计划进行分组,并应按年龄 ID 列求和,但我不知道该怎么做,这是我的尝试:

SELECT [Quote ID], [Deductible Plan],

case when [Age ID] = 'Age 65' THEN SUM([Number of Members]) else 0 END AS [Age 65],
case when [Age ID] = 'Age 85+' THEN SUM([Number of Members]) else 0 END AS [Age 85+]

FROM finalResult_001
GROUP BY [Quote ID], [Age ID], [Deductible Plan]

结果:

Quote ID Deductible Plan        Age 65      Age 85+
1381 $750 Deductible Plan 0 10
1381 $750 Deductible Plan 10 0
1371 $150 Deductible Plan 0 10
1371 $150 Deductible Plan 10 0

我如何对年龄 ID 进行求和来得到结果:

Quote ID Deductible Plan        Age 65      Age 85+
1381 $750 Deductible Plan 10 10
1371 $150 Deductible Plan 10 10

最佳答案

将 CASE 应用于 [Number of Members] 而不是 SUM([Number of Members]),并且不按 [Age ID]:

SELECT
[Quote ID],
[Deductible Plan],
SUM(case when [Age ID] = 'Age 65' THEN [Number of Members] else 0 END) AS [Age 65],
SUM(case when [Age ID] = 'Age 85+' THEN [Number of Members] else 0 END) AS [Age 85+]
FROM dbo.finalResult_001
GROUP BY [Quote ID], [Deductible Plan]
;

关于SQL Server Case 语句和聚合函数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25112213/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com