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)xi need the cumulative total(Running Total) for x.Field1 in a column.The result should look like...Field1 Cumulative Sum-----------------------------------1 12 33 64 10 RegardsVijay |
|
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 nametry this oneselect identity(int,1,1) as id ,* into #temp from tablenameselect amt,(select sum(amt) from #temp where id <= t.id ) as cumulativesum from #temp t |
|
|
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 NumberFROM master..spt_valuesWHERE Type = 'P' AND Number BETWEEN 1 AND 10ORDER BY NEWID()SELECT s.Col1, (SELECT SUM(x.Col1) FROM @Sample AS x WHERE x.Col1 <= s.Col1) AS CumulativeSumFROM @Sample AS sORDER BY s.Col1[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
vasss
Starting Member
2 Posts |
Posted - 2011-06-13 : 06:00:06
|
hi,SELECT a.field1,a.field2,SUM(b.field1) FROM ur tablename ainner JOIN ur table name bON (b.field2 <= a.field) GROUP BY a.field1,a.field1 |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-06-17 : 17:36:29
|
Calculate cummulative sum on the client not in sql.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
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" |
|
|
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.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-06-21 : 08:04:29
|
Thank you Peso.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
sandeepmittal11
Starting Member
6 Posts |
|
|