This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(140.8, 75%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESR%3C/text%3E%3C/svg%3E)
Hi Expert,
how to getting running some from col2
create table table1(col1 int)
insert into table1(10);
insert into table1(20);
insert into table1(30);
insert into table1(40);
Expected Output:
Total_col1
10
30
70
100
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
Additional SQL Server features and topics not covered by specific categories
Answer accepted by question author
CREATE TABLE #table1(col1 int)
INSERT INTO #table1 VALUES (10),(20),(30),(40)
SELECT
col1,
SUM(col1) OVER (ORDER BY col1) AS [runningTotal]
FROM #table1
DROP TABLE #table1
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(211.20000000000002, 61%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
cleaner sum, but without sequence, the following does not give the desired results
CREATE TABLE #table1(col1 int)
INSERT INTO #table1 VALUES (10),(20),(30),(40), (30), (40)
SELECT
col1,
SUM(col1) OVER (ORDER BY col1) AS [runningTotal]
FROM #table1
DROP TABLE #table1
but works with sequence
CREATE TABLE #table1 (col1 int, sequence int)
INSERT INTO #table1 VALUES (10,1),(20,2),(30,3),(40,4), (30,5), (40,6), (30,7), (40,8)
SELECT
col1,
SUM(col1) OVER (ORDER BY sequence) AS [runningTotal]
FROM #table1
DROP TABLE #table1
They simply want a growing number, they are inserting in order of col1, so it's assumed they want that same order. I provided the best option for what they have. There are scenarios where a sequence column is not needed. That is the desired results. If you notice they put "10,30,70,100"
You are assuming it's not the desired results because you didn't realize the math is wrong in their question. Their desired results are not 10,30,70,100; it's actually 10,30,60,100. So yes, I provided the actual results desired
There is not enough detail to know what the actual values would be. I’m just noting if duplicates or declining values are used the query won’t work.
Which is true and I made sure to vote your comments to bring light to it. It's scenario based, if they simply add an auto incremental column for their over clause, as per your suggestion, their partitioning will cover almost any scenario
Tables have no defined order. You will need a sequence column. Then the running total is just the sum of sequences less than or equal to the current sequence.
select
col1,
(select sum(col1)
from table1 t2
Where t2.sequence <= t1.sequence) as runningTotal
from table1 t1
order by t1.sequence
Better to avoid nested queries. There are much better methods out there that won't require a sequence column if you want your dataset ordered by the single column you have available