Net Sum in each row - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Net Sum in each row

Author

Topic

sentiboy
Starting Member

3 Posts

Posted - 2005-09-06 : 13:18:12

Hi,

I have to generate a report in which I need netqty as a field which should be equal to sum of "item qty" field in this and all previous rows.
e.g.
I am having following table
TableName: itemmaster
itemid itemname qty
1. Item1 2
2. Item2 4
3. Item3 5

I need new table from sql as :
itemid itemname qty netqty
1. Item1 2 2
2. Item2 4 6
3. Item3 5 11

Thanks
Bhavuk

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-06 : 13:23:29

How are you presenting or creating this report? It is much easier and more efficent to calculate running totals on the report itself, almost all reporting tools these days support those quite easily.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-09-07 : 04:52:08

SELECT ItemID, Itemname, Qty, (SELECT SUM(Qty) FROM table WHERE ItemID <= a.ItemID) AS NetQty
FROM table a
ORDER BY ItemID

I think this will work...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-08 : 01:32:49

It should be noted that using query to generate running total there will be performance issue if the table has millions of records. It is better to use Running Total option of Report

Bhavuk, which reporting tool are you using?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources