Running Sum on Query

Hye Guys,

I am new in this forums and programming..

I have data like

S.N Item Qty
1 ABC 10
2 ABC 20
3 ABC 5
4 XYZ 2
5 XYZ 10
6 XYZ 1

And I want the result

S.N Item Qty RunTot
1 ABC 10 10
2 ABC 20 30
3 ABC 5 35
4 XYZ 2 2
5 XYZ 10 12
6 XYZ 1 13
Plz Help meAre you going to be presenting these rows in a client application? If so,

it will be VASTLY more efficient to do these sub-totals there. It is easy

as you're looping one by one through the individual rows to keep a tally of

Qty for like Item values. With T-SQL, you will have to use a sub-query that

is evaluated for every row, and this is going to put your performance into

the toilet.

wrote in message

news:d85d7cec-a74b-47be-a43d-6b4c56306acd@.discussions.microsoft.com...

> Hye Guys,

>

> I am new in this forums and programming..

>

> I have data like

>

> S.N Item Qty

> 1 ABC 10

> 2 ABC 20

> 3 ABC 5

> 4 XYZ 2

> 5 XYZ 10

> 6 XYZ 1

>

> And I want the result

>

> S.N Item Qty RunTot

> 1 ABC 10 10

> 2 ABC 20 30

> 3 ABC 5 35

> 4 XYZ 2 2

> 5 XYZ 10 12

> 6 XYZ 1 13

>

>

> Plz Help me

>|||Try this one:

select t.[S.N], t.Item, t.Qty, sum(t1.Qty) as RunTot
from
YourTable as t
inner join YourTable as t1
on t.Item = t1.Item and t.[S.N]>=t1.[S.N]
group by t.[S.N], t.Item, t.Qty|||Here is an article on it. I have not tested it, I just came across it once and bookmarked it..

http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23349&DisplayTab=Article

Hope that helps!

BobP|||Hye friends,
The replies you have posted had helped me but as one of our friend said I tried it in a database containing large volumes of records.. and the result is ...... 2 slow. So need some other technique..

Always Ready 2 Learn
Kabindra Bakey|||Are there any reasons why it has to be in one select statement?

If it is for a report, let the reporting application calculate it.
If it is for an application, let the app calc it.

The other thing is to pre-process the statement, and store the results in a table, and just select from that table when you need the data.

You could setup the pre-process to happen at night, or every hour, etc... You would just need to dump the results to a temp table, start a transaction, delete from the processed table, populate the processed table from the temp table, and then commit the transaction.

To the users it is transparent, except that you dont have "real-time" data.

BobP|||

Dear guys,

Previously For such running totals I use to have a seperate column in a transaction table. And I used to calculate the value during the insertion of the record and store it in the respective column. that was quite easy way but by any means my rows get unsorted or the order of the transaction has to be changed manually I have w calculate the values again and maintain the column sum once more. which was totally manual calculation based. So I am trying to find the way out fo it...

Always there 2 learn
Kabindra Bakey