Questions

running sum in SQL

Tags:
+
0 Votes
Locked

running sum in SQL

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,
  • +
    0 Votes
    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

    +
    0 Votes
    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

    +
    0 Votes
    chapman.tim

    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#

    +
    0 Votes
    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.

    +
    0 Votes
    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.

    +
    0 Votes
    sgt_shultz

    i learned something, thanks!

  • +
    0 Votes
    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

    +
    0 Votes
    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

    +
    0 Votes
    chapman.tim

    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#

    +
    0 Votes
    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.

    +
    0 Votes
    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.

    +
    0 Votes
    sgt_shultz

    i learned something, thanks!