SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to find Cumulative Sum in a Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mvijayrkumar
Starting Member

6 Posts

Posted - 05/29/2009 :  06:19:49  Show Profile  Send mvijayrkumar a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 05/29/2009 :  06:23:44  Show Profile  Reply with Quote
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

Sweden
30241 Posts

Posted - 05/29/2009 :  06:25:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

vasss
Starting Member

India
2 Posts

Posted - 06/13/2011 :  06:00:06  Show Profile  Reply with Quote
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

Czech Republic
518 Posts

Posted - 06/17/2011 :  17:36:29  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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

Sweden
30241 Posts

Posted - 06/19/2011 :  02:39:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Czech Republic
518 Posts

Posted - 06/19/2011 :  12:38:11  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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

Sweden
30241 Posts

Posted - 06/19/2011 :  13:31:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Czech Republic
518 Posts

Posted - 06/21/2011 :  08:04:29  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
Thank you Peso.

Mirko

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

sandeepmittal11
Starting Member

India
6 Posts

Posted - 09/29/2012 :  09:07:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000