总数计算

Question

我正在使用以下数据和SQL编写存储过程。当我执行下面的SQL时，我得到每个月的计数，但是我希望计数在下个月做时加总。我想为查询添加一个额外的列（Totalcount），我期待如下所示的结果。提前致谢！

Month_NUMBER MonthlyCount Totalcount 
-------------------------------------- 
    1    4    4 
    2    1    5 
    3    1    6 
    4    2    8 

这是我目前使用SQL：

drop table #test 

create table #test (name varchar(10), MON_NUMBER int) 

insert into #test 
values ('XYZ', 1), ('ABC', 1), ('AZZ', 1), ('BCC', 1), 
     ('HAS', 2), ('MRD', 3), ('GIV', 4), ('GIVE', 4) 

SELECT 
    MON_NUMBER, 
    COUNT(NAME) AS MonthlyCount 
FROM 
    #test 
GROUP BY 
    MON_NUMBER 

Answer 1

您可以使用您的查询，如下：

Select *, Sum(MonthlyCount) over(order by Mon_nUmber) from (
    SELECT MON_NUMBER, COUNT(NAME) AS MonthlyCount 
    FROM #test 
    group by MON_NUMBER 
) a 

输出如下：

+------------+--------------+------------+ 
| MON_NUMBER | MonthlyCount | TotalCount | 
+------------+--------------+------------+ 
|   1 |   4 |   4 | 
|   2 |   1 |   5 | 
|   3 |   1 |   6 | 
|   4 |   2 |   8 | 
+------------+--------------+------------+ 

Answer 2

一个窗口函数可以在这里工作 - 给这个旋转。

SELECT DISTINCT 
    MON_NUMBER, 
    COUNT(NAME) AS MonthlyCount, 
    COUNT(name) OVER (ORDER BY mon_number) 
FROM #test 
group by MON_NUMBER, NAME 
Order by MON_NUMBER 

窗口化文档：https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Answer 3

[0]这类型计算的值被命名运行总数。

[1] SUM（）OVER（ORDER BY ...）仅开始SQL2012 +

[2]下面的解决方案应该是蛮好的旧版本（这里包括SQL2008）和少量数据：

create table #test (name varchar(10), MON_NUMBER int) 

insert into #test 
values ('XYZ', 201701), ('ABC', 201701), ('AZZ', 201701), ('BCC', 201701), 
     ('HAS', 201702), ('MRD', 201703), ('GIV', 201704), ('GIVE', 201704) 

;WITH BaseQuery 
AS (
SELECT 
    MON_NUMBER, 
    COUNT(NAME) AS MonthlyCount 
FROM 
    #test 
GROUP BY 
    MON_NUMBER 
) 
SELECT *, (
      SELECT SUM(x.MonthlyCount) FROM BaseQuery x 
      WHERE x.MON_NUMBER <= bq.MON_NUMBER 
     ) AS MonthlyCountRunningTotal 
FROM BaseQuery bq 

Answer 4

您可以使用自联接在SQL Server 2008：

WITH CTE AS (
    SELECT MON_NUMBER, COUNT(NAME) AS MonthlyCount 
    FROM #test 
    group by MON_NUMBER 
) 
SELECT C1.MON_NUMBER, C1.MonthlyCount, SUM(C2.MonthlyCount) AS TotalCount 
FROM CTE C1 
JOIN CTE C2 ON C1.MON_NUMBER >= C2.MON_NUMBER 
GROUP BY C1.MON_NUMBER, C1.MonthlyCount 
ORDER BY C1.MON_NUMBER, C1.MonthlyCount; 

Answer 5

@ S.Yang已经给出了最好的和适当的答案。由于SUM（）窗口函数不能在SQL Server 2008中使用，因此您必须使用自连接。基于他的回答，我提交了另一个解决方案，该解决方案通过不使用CTE而对大型数据集非常有用。

IF OBJECT_ID(N'dbo.temp', N'U') IS NOT NULL DROP TABLE dbo.temp; 
SELECT MON_NUMBER, COUNT(NAME) AS MonthlyCount 
INTO dbo.temp 
FROM #test 
GROUP BY MON_NUMBER; 

-- Create Index 
CREATE INDEX IX_dbo_temp ON dbo.temp (MON_NUMBER, MonthlyCount); 

SELECT t1.MON_NUMBER, t1.MonthlyCount, SUM(t2.MonthlyCount) AS TotalCount 
FROM dbo.temp AS t1 
INNER JOIN dbo.temp AS t2 ON (t2.MON_NUMBER <= t1.MON_NUMBER) 
GROUP BY t1.MON_NUMBER, t1.MonthlyCount; 

-- Drop temporary tables 
IF OBJECT_ID(N'dbo.temp', N'U') IS NOT NULL DROP TABLE dbo.temp;