我有以下代码:
SELECT
ehrprg.ReportName
,ehrprg.AnnualGoalServiceMinutes
,COUNT(DISTINCT ct.[ClientFK]) AS [UnduplicatedClients]
FROM
[WH].[Fact].[EHRClinicalTransaction] ct
INNER JOIN [Dimension].EHRProgram ehrprg ON
ct.ProgramFK = ehrprg.WHID
WHERE
ehrprg.AnnualGoalServiceMinutes > 0
GROUP BY
ehrprg.ReportName
,ehrprg.AnnualGoalServiceMinutes
ORDER BY
ReportName
结果:
但我需要它在[ReportName]列中只有一个“SM NV”(而不是 2 个)和一个“SM REACH”(而不是 3 个)行,总结[ServiceMinutes]
当我使用SUM(ehrprg.AnnualGoalServiceMinutes) 时,它给了我“算术溢出错误转换为数据类型 int ”错误。
然后,我尝试了SUM(CONVERT(BIGINT, ehrprg.AnnualGoalServiceMinutes)),但得到以下结果:
它仍然没有分组(没有总结AnnualGoalServiceMinutes)并给出了一些值,我无法理解
我的目标是看到而不是——
ReportName AnnualGoalServiceMin
SM NV 197885
SM NV 348654
SM REACH 40000
SM REACH 80000
SM REACH 380000
我期望AnnualGoalServiceMin 的总和:
ReportName AnnualGoalServiceMin
SM NV 546539
SN REACH 500000
请帮忙
我不知道溢出错误的来源(它不应该根据 invovled 整数的大小发生),但我认为您在这里需要的是第二级聚合:
WITH cte AS (
SELECT
ehrprg.ReportName,
ehrprg.AnnualGoalServiceMinutes,
COUNT(DISTINCT ct.[ClientFK]) AS UnduplicatedClients
FROM [WH].[Fact].[EHRClinicalTransaction] ct
INNER JOIN [Dimension].EHRProgram ehrprg
ON ct.ProgramFK = ehrprg.WHID
WHERE ehrprg.AnnualGoalServiceMinutes > 0
GROUP BY ehrprg.ReportName, ehrprg.AnnualGoalServiceMinutes
)
SELECT
ReportName,
SUM(AnnualGoalServiceMinutes) AS AnnualGoalServiceMinutes,
SUM(UnduplicatedClients) AS UnduplicatedClients
FROM cte
GROUP BY
ReportName;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句