Calculating the running total

Question

I want to calculate the running total using a Stored Procedure. The base Table is ~10.000 rows and is as follows:

nWordNr nBitNr  tmTotals
------------------------
5       14      86404
5       14      146
2       3       438
10      2       3319
5       12      225
2       3       58
....    ....    .....
....    ....    .....

I want this to be GROUPED BY nWordNr, NBitNr and have the total tmTotals. To do this is started of with the following:

SELECT TOP 10 
[nWordNr] as W, 
[nBitNr] as B,
SUM([tmTotals]) as total,
COUNT(*) as Amount

FROM Messages_History
GROUP BY nWordNr, nBitNr
ORDER BY total desc

This results in:

W   B   total     Amount
-----------------------
2   3   3578775   745
3   3   3557975   395
5   4   2305229   72
5   3   2183050   33
5   12  2022401   825
5   14  1673295   652
48  12  1658862   302
4   3   1606454   215
48  13  1541729   192
5   9   1463256   761

Now I want to calculate the running total on the column total like this:

W   B   total     Amount   running
-------------------------------
2   3   3578775   745      3578775    
3   3   3557975   395      7136750
5   4   2305229   72       9441979
5   3   2183050   33       11625029
5   12  2022401   825      etc.
5   14  1673295   652      etc.
48  12  1658862   302      etc.
4   3   1606454   215      etc.
48  13  1541729   192      etc.
5   9   1463256   761      etc.

so what I found was:

COUNT([tmTotals]) over (ORDER BY [nWordNr], [nBitNr]) as Running

But here I get the error that is discussed in this question: Column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause and I just can't figure out how to solve it in this case

You suppose to use `SUM` not `COUNT` aggregate function in order to calculate running total — Jaydip Jadhav, Mar 06 '19 at 07:27

Squirrel · Accepted Answer · 2019-03-06T07:42:35.667

it should be SUM ( SUM ( tmTotals) ) OVER ( ... )

SELECT TOP 10 
       [nWordNr] as W, 
       [nBitNr] as B,
       SUM([tmTotals]) as total,
       COUNT(*) as Amount,
       SUM(SUM([tmTotals])) OVER (ORDER BY [nWordNr], [nBitNr]) as Running
FROM   Messages_History
GROUP BY nWordNr, nBitNr
ORDER BY total desc

EDIT : Looking at your expected result, the Running should be

SUM(SUM([tmTotals])) OVER (ORDER BY SUM([tmTotals]) DESC) as Running

if the above is a bit difficult to grasp, then you can use a CTE or derived table and perform the running total on the outer query

; with CTE as
(
    SELECT 
         [nWordNr] as W, 
         [nBitNr] as B,
         SUM([tmTotals]) as total,
         COUNT(*) as Amount
    FROM Messages_History
    GROUP BY nWordNr, nBitNr
)
SELECT TOP 10 *, 
       SUM(total) OVER (ORDER BY total desc) as Running
FROM   CTE
ORDER BY total desc

Calculating the running total

1 Answers1