| Author |
 Topic  |
|
|
mvijayrkumar
Starting Member
6 Posts |
 Posted - 05/29/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 05/29/2009 : 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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/29/2009 : 06:25:23
|
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" |
|
|
|
vasss
Starting Member
India
2 Posts |
Posted - 06/13/2011 : 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 |
|
|
|
mmarovic
Aged Yak Warrior
Czech Republic
518 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/19/2011 : 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
Czech Republic
518 Posts |
Posted - 06/19/2011 : 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/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
|
|
mmarovic
Aged Yak Warrior
Czech Republic
518 Posts |
|
|
sandeepmittal11
Starting Member
India
6 Posts |
|
| |
Topic |
|
How to find Cumulative Sum in a Table
