id="info"

Question

Locked

running sum in SQL

By highlander718 ·
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,

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Don't do it in SQL would be my advice

by Tony Hopkinson In reply to running sum in SQL

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

Collapse -

If you really must...

by steve.ingle In reply to Don't do it in SQL would ...

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

Collapse -

How to do it in SQL Server

by chapman.tim In reply to If you really must...

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#

Collapse -

Just say NO

by bluemoonsailor In reply to running sum in SQL

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.

Collapse -

I Agree

by kerry.millen In reply to Just say NO

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.

Collapse -

helpful post, bluemoon, thank you!

by sgt_shultz In reply to Just say NO

i learned something, thanks!

Back to Software Forum
7 total posts (Page 1 of 1)  

Software Forums