How to get cumulative sum
select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id
SQL Fiddle example
Output
| ID | SOMENUMT | SUM |
-----------------------
| 1 | 10 | 10 |
| 2 | 12 | 22 |
| 3 | 3 | 25 |
| 4 | 15 | 40 |
| 5 | 23 | 63 |
Edit: this is a generalized solution that will work across most db platforms. When there is a better solution available for your specific platform (e.g., gareth's), use it!
How can i do cumulative total in SQL Server?
First, you need a column that specifies the ordering, because SQL tables represent unordered sets. Let me assume you have such a column.
Then the function is sum()
as a window function:
select t.*,
sum(amount) over (partition by company order by <ordering col>)
from t;
Note: This does not return 0
for the "first" row for each company, so it really is a cumulative sum. For your logic, you need an additional conditional:
select t.*,
(case when row_number() over (partition by company order by <ordering col>) = 1
then 0
else sum(amount) over (partition by company order by <ordering col>)
end)
from t;
Calculating cumulative sum in ms-sql
You can use CROSS APPLY
:
SELECT
t1.*,
x.cumulative
FROM tblSumDemo t1
CROSS APPLY(
SELECT
cumulative = SUM(t2.Percent_of_Qty)
FROM tblSumDemo t2
WHERE t2.billingid <= t1.billingid
)x
For SQL Server 2012+, you can use SUM OVER()
:
SELECT *,
cummulative = SUM(Percent_of_Qty) OVER(ORDER BY billingId)
FROM tblSumDemo
Sum of total by date with running total
Here's a version that squeezes the size of the code down compared to other examples. It should also be a little faster than some as there's no sub queries forcing multiple reads of the table. The trick to remember when using running totals and a group by statement is that you don't apply the window function to the raw column but to an aggregate of the raw column (hence I nest a SUM inside a SUM).
SELECT
CAST(OrderCheckOutDate AS date) AS OrderCheckOutDate,
SUM (OrderTotalFixed) AS DayTotal,
SUM (SUM (OrderTotalFixed)) OVER (ORDER BY CAST(OrderCheckOutDate AS date)) AS RunningTotal
FROM
tblOrder
Where
UserExternalUserLevel = 0
And
OrderCheckOutDate > '2019-11-01'
GROUP BY CAST(OrderCheckOutDate AS date)
You can see this working at dbfiddle
If your actual use case is more complicated for example you want the running total to reset each month that can be handled by adding PARTITION in to the window function.
Partitioning by month will look something like this:
SELECT
CAST(OrderCheckOutDate AS date) AS OrderCheckOutDate,
EOMONTH(CAST(OrderCheckOutDate AS date)) AS MonthEnd,
SUM (OrderTotalFixed) AS DayTotal,
SUM (SUM (OrderTotalFixed)) OVER (PARTITION BY EOMONTH(CAST(OrderCheckOutDate AS date)) ORDER BY CAST(OrderCheckOutDate AS date) ) AS RunningTotal
FROM
tblOrder
Where
UserExternalUserLevel = 0
And
OrderCheckOutDate > '2019-11-01'
GROUP BY CAST(OrderCheckOutDate AS date)
And again see it in action with dbfiddle
Calculate a Running Total in SQL Server
Update, if you are running SQL Server 2012 see: https://stackoverflow.com/a/10309947
The problem is that the SQL Server implementation of the Over clause is somewhat limited.
Oracle (and ANSI-SQL) allow you to do things like:
SELECT somedate, somevalue,
SUM(somevalue) OVER(ORDER BY somedate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS RunningTotal
FROM Table
SQL Server gives you no clean solution to this problem. My gut is telling me that this is one of those rare cases where a cursor is the fastest, though I will have to do some benchmarking on big results.
The update trick is handy but I feel its fairly fragile. It seems that if you are updating a full table then it will proceed in the order of the primary key. So if you set your date as a primary key ascending you will probably
be safe. But you are relying on an undocumented SQL Server implementation detail (also if the query ends up being performed by two procs I wonder what will happen, see: MAXDOP):
Full working sample:
drop table #t
create table #t ( ord int primary key, total int, running_total int)
insert #t(ord,total) values (2,20)
-- notice the malicious re-ordering
insert #t(ord,total) values (1,10)
insert #t(ord,total) values (3,10)
insert #t(ord,total) values (4,1)
declare @total int
set @total = 0
update #t set running_total = @total, @total = @total + total
select * from #t
order by ord
ord total running_total
----------- ----------- -------------
1 10 10
2 20 30
3 10 40
4 1 41
You asked for a benchmark this is the lowdown.
The fastest SAFE way of doing this would be the Cursor, it is an order of magnitude faster than the correlated sub-query of cross-join.
The absolute fastest way is the UPDATE trick. My only concern with it is that I am not certain that under all circumstances the update will proceed in a linear way. There is nothing in the query that explicitly says so.
Bottom line, for production code I would go with the cursor.
Test data:
create table #t ( ord int primary key, total int, running_total int)
set nocount on
declare @i int
set @i = 0
begin tran
while @i < 10000
begin
insert #t (ord, total) values (@i, rand() * 100)
set @i = @i +1
end
commit
Test 1:
SELECT ord,total,
(SELECT SUM(total)
FROM #t b
WHERE b.ord <= a.ord) AS b
FROM #t a
-- CPU 11731, Reads 154934, Duration 11135
Test 2:
SELECT a.ord, a.total, SUM(b.total) AS RunningTotal
FROM #t a CROSS JOIN #t b
WHERE (b.ord <= a.ord)
GROUP BY a.ord,a.total
ORDER BY a.ord
-- CPU 16053, Reads 154935, Duration 4647
Test 3:
DECLARE @TotalTable table(ord int primary key, total int, running_total int)
DECLARE forward_cursor CURSOR FAST_FORWARD
FOR
SELECT ord, total
FROM #t
ORDER BY ord
OPEN forward_cursor
DECLARE @running_total int,
@ord int,
@total int
SET @running_total = 0
FETCH NEXT FROM forward_cursor INTO @ord, @total
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @running_total = @running_total + @total
INSERT @TotalTable VALUES(@ord, @total, @running_total)
FETCH NEXT FROM forward_cursor INTO @ord, @total
END
CLOSE forward_cursor
DEALLOCATE forward_cursor
SELECT * FROM @TotalTable
-- CPU 359, Reads 30392, Duration 496
Test 4:
declare @total int
set @total = 0
update #t set running_total = @total, @total = @total + total
select * from #t
-- CPU 0, Reads 58, Duration 139
SQL Server Cumulative Sum breaks
The easiest way to do this is with a recursive CTE. These are effectively loops, where the next value is calculated based on previous values. Here's a link to a basic example and writeup of how they work: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/
In this case, it checks if the previous value + the current value is over 5000; if so, it resets the counter and adds 1 to the GroupNo; otherwise it just adds the current value to CumulativeSum.
CREATE TABLE #Amts (ID int PRIMARY KEY, Amount int);
INSERT INTO #Amts (ID, Amount)
VALUES
(1, 1000), -- | 1000 | 0 |
(2, 2000), -- | 3000 | 0 |
(3, 1000), -- | 4000 | 0 |
(4, 3000), -- | 3000 | 1 |
(5, 2000), -- | 5000 | 1 |
(6, 3000), -- | 3000 | 2 |
(7, 1000), -- | 4000 | 2 |
(8, 4000), -- | 4000 | 3 |
(9, 2000); -- | 2000 | 4 |
WITH RunningTotals AS
(SELECT A.ID, A.Amount, A.Amount AS CumulativeSum, 0 AS GroupNo
FROM #Amts A
WHERE ID = 1
UNION ALL
SELECT RT.ID + 1,
A.Amount,
CASE WHEN RT.CumulativeSum + A.Amount > 5000 THEN A.Amount
ELSE RT.CumulativeSum + A.Amount END,
CASE WHEN RT.CumulativeSum + A.Amount > 5000 THEN RT.GroupNo + 1
ELSE RT.GroupNo END
FROM RunningTotals RT
INNER JOIN #Amts A ON RT.ID + 1 = A.ID
)
SELECT *
FROM RunningTotals
OPTION (MAXRECURSION 1000);
Results are as follows
ID Amount CumulativeSum GroupNo
1 1000 1000 0
2 2000 3000 0
3 1000 4000 0
4 3000 3000 1
5 2000 5000 1
6 3000 3000 2
7 1000 4000 2
8 4000 4000 3
9 2000 2000 4
Notes
- This currently requires the IDs to be consecutive and starting with 1. If not, you may need to make an initial CTE with
ROW_NUMBER() OVER (ORDER BY Id)
to get this - I've put MAXRECURSION 1000 on there - that means it will do this looping 1000 times. If you have a larger table than this, you'll need to increase that number.
Update: fixed bug in code ( RT.GroupNo + 1
was in wrong spot). Also added 'Amount' column to output.
Adding a running total column in SQL Server
try SUM() OVER
SELECT
item,
week,
amount,
SUM(amount) over (partition by item order by Week) as Total
FROM yourTable
Running total per group in SQL Server 2008
The best way to do this is with cumulative sum. Perhaps it is time to update to a more recent version of SQL Server?
In SQL Server 2008, you can use apply
or a correlated subquery:
Select [Group], [Day], sum(money) as [Money],
(select sum(t2.money)
from table_name t2
where t.group = t2.group and t2.day <= t.day
) as Column4
From Table_Name t
Group by [Group], [Day];
Running total by date/ID based on latest change to value SQL
I added a few more rows just in case if this is what you really wanted.
I used T-SQL.
declare @orig table(
id int,
quantity int,
rundate date
)
insert into @orig
values (1,9,'20220324'),(2072,33,'20220324'),(2072,31,'20220326'),(2072,31,'20220327'),
(2,10,'20220301'),(2,20,'20220325'),(2,30,'20220327')
declare @dates table (
runningdate date
)
insert into @dates
select distinct rundate from @orig
order by rundate
declare @result table (
dates date,
running_quality int
)
DECLARE @mydate date
DECLARE @sum int
-- CURSOR definition
DECLARE my_cursor CURSOR FOR
SELECT * FROM @dates
OPEN my_cursor
-- Perform the first fetch
FETCH NEXT FROM my_cursor into @mydate
-- Check @@FETCH_STATUS to see if there are any more rows to fetch
WHILE @@FETCH_STATUS = 0
BEGIN
;with cte as (
select * from @orig
where rundate <= @mydate
), cte2 as (
select id, max(rundate) as maxrundate
from cte
group by id
), cte3 as (
select a.*
from cte as a join cte2 as b
on a.id = b.id and a.rundate = b.maxrundate
)
select @sum = sum(quantity)
from cte3
insert into @result
select @mydate, @sum
-- This is executed as long as the previous fetch succeeds
FETCH NEXT FROM my_cursor into @mydate
END -- cursor
CLOSE my_cursor
DEALLOCATE my_cursor
select * from @result
Result:
dates running_quality
2022-03-01 10
2022-03-24 52
2022-03-25 62
2022-03-26 60
2022-03-27 70
Related Topics
Group Datetime into 5,15,30 and 60 Minute Intervals
Ora-00972 Identifier Is Too Long Alias Column Name
Get Avg Ignoring Null or Zero Values
Why Don't Dbms's Support Assertion
Getting SQL Server Cross Database Dependencies
Using Like in an Oracle in Clause
T-SQL Skip Take Stored Procedure
Oracle in VS Exists Difference
MySQL Strip Time Component from Datetime
Execute a Stored Procedure in Another Stored Procedure in SQL Server
Suggestions for Implementing Audit Tables in SQL Server
Select Latest Row for Each Group from Oracle
Postgresql Column 'Foo' Does Not Exist
Doctrine Query Builder Using Inner Join with Conditions
How to Get Last Day of Last Week in SQL