正在运行累积SUM

Question

我有一个大型(ish(成员数据库，我正试图在其中对未来的成员身份进行一些预测。因此，我试图获得每个月输入系统的成员的累计总数。

我有以下代码

select  YEAR(datejoined) as YDJ,
MONTH(datejoined) as MDJ,
COUNT(MONTH(datejoined)) as CDJ,
SUM(Count(Month(DateJoined))) as SCDJ
From Member
Group by YEAR(datejoined), MONTH(datejoined)
Order by YEAR(DateJoined) asc, MONTH(datejoined) asc;

然而，它抛出了一个错误，如下所示：

Msg130，级别15，状态1，第4行无法对包含聚合或子查询的表达式执行聚合函数。

我希望SUM函数所做的是计算从开始到该点DB中的总成员数(包括当月的新成员(。

Answer 1

SUM(Count(Month(DateJoined))) as SCDJ引起的错误

聚合函数不能在一个选择中包含

聚集函数你需要写一个子查询来解决它

如果您想累积总和，您可以将窗口函数与SUM一起使用

SELECT t.*,SUM(CDJ) OVER (ORDER BY YDJ ,MDJ) as SCDJ
FROM (
select  
YEAR(datejoined) as YDJ,
MONTH(datejoined) as MDJ,
COUNT(datejoined) as CDJ
From Member
Group by YEAR(datejoined), MONTH(datejoined)
) t 
Order by YDJ asc, 
MDJ asc

Answer 2

根据您的描述，我认为您想要：

select YEAR(datejoined) as YDJ, MONTH(datejoined) as MDJ,
COUNT(*) as CDJ,
SUM(COUNT(*)) OVER (ORDER BY YEAR(datejoined), MONTH(datejoined)) as running_CDJ
SUM(Count(Month(DateJoined))) as SCDJ
From Member
Group by YEAR(datejoined), MONTH(datejoined)
Order by YEAR(DateJoined) asc, MONTH(datejoined) asc;

SQL Server 2012+中提供了累计金额。在早期版本中，您可以使用APPLY:

with t as (
select YEAR(datejoined) as YDJ, MONTH(datejoined) as MDJ,
COUNT(*) as CDJ,
SUM(COUNT(*)) OVER (ORDER BY YEAR(datejoined), MONTH(datejoined)) as running_CDJ
From Member
Group by YEAR(datejoined), MONTH(datejoined)
)
select t.*, t2.running_cdj
from t outer apply
(select sum(CDJ) as running_CDJ
from t t2
where t2.ydj < t.ydj or
t2.ydj = t.ydj and t2.mdj <= t2.mdj
) t2;