sql - 将来自三个不同查询的数字加在一起

Question

我正在获取过去 3、6 和 10 个月不同的平均值。来自以下查询

select SUM(GrossAmount)/10 as Averageten  
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -10, GETDATE()) 

select SUM(GrossAmount)/6 as Averagesix  
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -6, GETDATE()) 

select SUM(GrossAmount)/3 as Averagethree  
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -3, GETDATE())

例如，我得到三个不同的值:

1200.22
2300.22
4500

我想像这样得到这三个值的平均值

(1200.22 + 2300.22 + 4500) / 3

如何从这些单独的查询中添加值。

Answer 1

用union合并三个query然后做average

select avg(Averageten)
from
(select SUM(GrossAmount)/10 as Averageten  
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -10, GETDATE()) 
union
select SUM(GrossAmount)/6 
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -6, GETDATE()) 
union 
select SUM(GrossAmount)/3   
FROM Table
WHERE CreatedDate >= DATEADD(MONTH, -3, GETDATE())
)a

或者您可以使用条件聚合来查找值，因为您查询的是来自具有不同条件的同一个表

select ((sum(case when CreatedDate >= DATEADD(MONTH, -10, GETDATE()) then GrossAmount end)/10)
+(sum(case when CreatedDate >= DATEADD(MONTH, -6, GETDATE()) then GrossAmount end)/6)
+(sum(case when CreatedDate >= DATEADD(MONTH, -3, GETDATE()) then GrossAmount end)/3))/3 
from tablename