按条件过滤值的平均值

Question

在执行下面的查询时，出现以下消息:

Msg 130，不能对包含聚合或子查询的表达式执行聚合函数。

SELECT DATEADD(n,-10,[TimeStampLocalSystem]) as Date,
AVG(CASE WHEN [Minute10Average]>0 THEN [Minute10Average] END) AS Average,
AVG(CASE WHEN ABS(1-Minute10Average/AVG(CASE WHEN [Minute10Average]>0 THEN [Minute10Average] END))<0.5 THEN Minute10Average END) AS Average_corr
FROM [XXX]
INNER JOIN [XXX]
ON [XXX].Systemnumber=[YYY].SystemNumber
WHERE [TimeStampLocalSystem] BETWEEN '2022-09-16 17:10:00' AND '2022-09-16 18:20:00' 
AND [DataPointID] IN (XXX)
AND RIGHT(FORMAT([TimeStampLocalSystem],'DD.MM.YYYY hh:mm:ss'),4) = '0:00'
GROUP BY [TimeStampLocalSystem]

问题在这一行:

AVG(CASE WHEN ABS(1-Minute10Average/AVG(CASE WHEN [Minute10Average]>0 THEN [Minute10Average] END))<0.5 THEN Minute10Average END) AS Average_corr

你能帮我定义一下什么是错的吗?

Answer 1

使用Having和聚合过滤器

select name, sum(field) from table group by name having sum(field)>1