Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to find Cumulative Sum in a Table

Author  Topic 

mvijayrkumar
Starting Member

6 Posts

Posted - 2009-05-29 : 06:19:49
Hi guys.....

can u pls help me to solve this issue...

select x.Field1 from
(
select T.Field1,T.Field2 from Table1 T
)x

i need the cumulative total(Running Total) for x.Field1 in a column.

The result should look like...

Field1 Cumulative Sum
-----------------------------------
1 1
2 3
3 6
4 10





Regards

Vijay



bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 06:23:44
if u dont have any identity column in ur table name
try this one
select identity(int,1,1) as id ,* into #temp from tablename

select amt,(select sum(amt) from #temp where id <= t.id ) as cumulativesum from #temp t
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 06:25:23
[code]DECLARE @Sample TABLE
(
Col1 INT
)

INSERT @Sample
(
Col1
)
SELECT TOP 4 Number
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 1 AND 10
ORDER BY NEWID()

SELECT s.Col1,
(SELECT SUM(x.Col1) FROM @Sample AS x WHERE x.Col1 <= s.Col1) AS CumulativeSum
FROM @Sample AS s
ORDER BY s.Col1[/code]


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

vasss
Starting Member

2 Posts

Posted - 2011-06-13 : 06:00:06
hi,


SELECT a.field1,a.field2,SUM(b.field1)
FROM ur tablename a
inner JOIN ur table name b
ON (b.field2 <= a.field)
GROUP BY a.field1,a.field1
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-17 : 17:36:29
Calculate cummulative sum on the client not in sql.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-19 : 02:39:42
Even if you are using the result for BCP to a file?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-19 : 12:38:11
quote:
Originally posted by Peso

Even if you are using the result for BCP to a file?



N 56°04'39.26"
E 12°55'05.63"


What are you going to do with file? If you bcp raw data you can calculate running total at any point in the process. The running total is info that can always be derived from the data set returned. It just happens that it is more efficient and less resource intensive to do it on the client. I wrote an article about the topic a few years ago. I wanted to show how inefficient or unreliable is calculating it in sql. To my surprise, many readers liked the technique later named "quirky updates" by Jeff Moden even it was proven to be not quite reliable. So, now I am reluctant to provide links to these articles.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-19 : 13:31:44
Then you will love the next version of SQL Server.
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI310



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-21 : 08:04:29
Thank you Peso.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

sandeepmittal11
Starting Member

6 Posts

Posted - 2012-09-29 : 09:07:00
Refer below posts
http://itdeveloperzone.blogspot.in/2011/03/cumulative-sum-in-sql-server.html
http://itdeveloperzone.blogspot.in/2012/02/running-total-in-sql-server.html

Regards,
Sandeep
Go to Top of Page
   

- Advertisement -