I have a sorted table and I want to create another one that would contain a field that is a running sum. I cannot figure it out in SQL, I know it's possible in Excel, Access ...
day total for day running sum
1 150 150
2 50 200
3 -120 80
...etc
Thanks,
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (2)
0
Votes
Don't do it in SQL would be my advice
SQL is set based running sum is not, it's sequence based.
Personally I'd do this client side s Excel and access do it.
In SQL the ways round it are effectively a sub query
Select Sum(Value) where day =3 in your example. Different sorts would have an impact and you might end up with an order by in the subquery. Select and two pass sort will cost you big style, on a big resultset.
Or using a cursor, which generally should be avoided, if at all possible
Personally I'd do this client side s Excel and access do it.
In SQL the ways round it are effectively a sub query
Select Sum(Value) where day =3 in your example. Different sorts would have an impact and you might end up with an order by in the subquery. Select and two pass sort will cost you big style, on a big resultset.
Or using a cursor, which generally should be avoided, if at all possible
20th Jul 2007
Replies
I would normally do this calculation on the fly, when compiling a report, or displaying a particular record (using Excel, Access, etc).
But, if you REALLY have to hold the running sum in a fixed field in the database, then you could use a trigger to test for changes in the field on which the calculation is based.
How much of a performance hit you would take if you use a trigger depends on the complexity of the calculation and the number of times the trigger is fired. You would also need to be aware of concurrency/locking issues.
Steve
But, if you REALLY have to hold the running sum in a fixed field in the database, then you could use a trigger to test for changes in the field on which the calculation is based.
How much of a performance hit you would take if you use a trigger depends on the complexity of the calculation and the number of times the trigger is fired. You would also need to be aware of concurrency/locking issues.
Steve
steve.ingle@...
24th Jul 2007
This article will explain how you can use TSQL to create a running subtotal in a query.
http://articles.techrepublic.com.com/5100-9592_11-6100447.html#
http://articles.techrepublic.com.com/5100-9592_11-6100447.html#
chapman.tim@...
27th Jul 2007
0
Votes
Just say NO
Couple o' quick questions - how should your running total change if you did a query that only returned every other row? Or only rows for a single customer/product/account? What happens if you sort by account then by date? Or if you order the data by region?
SQL is set based. You can extract many different sets from the same data, and SQL, by definition, does not provide a guaranteed order to the data. This is important because your running total is VERY dependent on the order of the data and the set that is retrieved.
If you need to provide a running total, do it in your reporting tool. SQL is not a spreadsheet and shouldn't be used like one.
Take a couple of SQL classes and/or talk to your DBA. SQL is a tool that can do some really wonderful things for you, but you have to know how to use the tool first!
Steve G.
SQL is set based. You can extract many different sets from the same data, and SQL, by definition, does not provide a guaranteed order to the data. This is important because your running total is VERY dependent on the order of the data and the set that is retrieved.
If you need to provide a running total, do it in your reporting tool. SQL is not a spreadsheet and shouldn't be used like one.
Take a couple of SQL classes and/or talk to your DBA. SQL is a tool that can do some really wonderful things for you, but you have to know how to use the tool first!
Steve G.
24th Jul 2007
Replies
Even if a specific order could be extracted based on an invoice number or date/time stamp, a record field name holding a running sum would be a poor use of disk space. A query based on IDs or any specific field ordering you choose could extract a running total in a more efficient manner.
kerry.millen@...
25th Jul 2007
i learned something, thanks!
sgt_shultz
25th Jul 2007
