时间戳

首页 > 解决方案 > 运行累积 SUM

sql - 运行累积 SUM

问题描述

我有一个大型（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;

但是它抛出如下错误：

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

我希望 SUM 函数做的是计算数据库中从开始到该点的总成员数（包括该月的新成员）。

标签： sqlsql-server

解决方案

导致的错误SUM(Count(Month(DateJoined))) as SCDJ

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

您需要编写一个子查询来解决它

如果要累积 SUM，可以使用窗口函数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

推荐阅读