Software

Ask something
Question Locked

running sum in SQL

0 helpful
7 replies

by highlander718

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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,

7 replies

by tony hopkinson

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

by steve.ingle

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

by bluemoonsailor

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.

by kerry.millen

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.

This discussion is closed to new replies.