MSSQL 总和与大小写查询错误

Question

我一直在尝试完成以下查询，但我似乎无法让它工作。

我想知道是否有人能明显地看到我做错了？

MSSQL 服务器 2012.

我已经测试了连接和一些子查询，正如您从查询本身中看到的那样，它们工作正常。

有什么想法吗？

SELECT b.CLIENT_NAME
,e.VARIABLE_NAME
--,[TOTAL]
--,AVG(a.TOTAL) AS [Total KPI]
--, (SUM(d.WEEK1_NET)+SUM(d.WEEK2_NET)+SUM(d.WEEK3_NET)+SUM(d.WEEK4_NET))AS [Net Sales]
,SUM(
case 
when a.GRAPH_ID =  1 then 100/(SUM(d.WEEK1_NET)+SUM(d.WEEK2_NET)+SUM(d.WEEK3_NET)+SUM(d.WEEK4_NET))*SUM(a.TOTAL) 
when a.GRAPH_ID =  2 then 100/ (SUM(d.WEEK1_GROSS)+SUM(d.WEEK2_GROSS)+SUM(d.WEEK3_GROSS)+SUM(d.WEEK4_GROSS))*SUM(a.TOTAL)
else AVG(a.TOTAL) 
end) AS Project_KPI 
FROM [MMAUDIT].[dbo].[MMA_KPIVAR_DAT] AS a
LEFT JOIN MMA_CLIENT_MST AS b
ON a.CLIENT_ID= b.CLIENT_ID
LEFT JOIN MMA_SITE_MST AS c
ON a.CLIENT_ID= c.CLIENT_ID
AND a.SITE_ID= c.SITE_ID
LEFT JOIN MMA_SALESPERF_DAT AS d
ON a.CLIENT_ID= d.CLIENT_ID
AND a.SITE_ID= d.SITE_ID
AND a.AUDIT_DATE= d.AUDIT_DATE
LEFT JOIN MMA_KPIVAR_MST As e
ON a.CLIENT_ID= e.CLIENT_ID
AND a.CHART_ID=e.CHART_ID
AND a.GRAPH_ID= e.GRAPH_ID
AND a.VARIABLE_ID= e.VARIABLE_ID
WHERE a.CLIENT_ID= 18
AND a.AUDIT_DATE BETWEEN '2017-01-01' AND '2017-07-01'
--AND a.GRAPH_ID IN (3,4,5,6)
GROUP BY b.CLIENT_NAME
,e.VARIABLE_NAME
--,[TOTAL]'

谢谢约翰

Answer 1

不能有嵌套的 SUM 或其他聚合。这里有一个简单的例子。

SUM(
case 
when a.GRAPH_ID =  1 then 100/(SUM(d.WEEK1_NET)+SUM(d.WEEK2_NET)+SUM(d.WEEK3_NET)+SUM(d.WEEK4_NET))*SUM(a.TOTAL) 
when a.GRAPH_ID =  2 then 100/ (SUM(d.WEEK1_GROSS)+SUM(d.WEEK2_GROSS)+SUM(d.WEEK3_GROSS)+SUM(d.WEEK4_GROSS))*SUM(a.TOTAL)
else AVG(a.TOTAL) 
end)

是一个问题，因为它将 SUM 与 SUM 和 AVG 嵌套在一起，并且计算不明确。最简单的解决方法是在您的子查询中执行内部 SUM：SUM(d.WEEK1_NET( 等，然后在主查询中使用 case 语句中的值。