months worth of data in database table help

Question

I have a table of records like the following

Month Amount  Sum
1     100     100
2     50      150
3     NULL    NULL
4     NULL    NULL
5     50      200
ETC.

How do I keep a running total sum column and I'd like to cascade the previous valid sum into null records like follows in one SQL Statement?

1     100     100
2     50      150
3     0       150
4     0       150
5     50      200

Any ideas?

score 3 · Answer 1 · edited Sep 21 '11 at 19:52

3

This isn't something you'd typically store in the database, but rather get with a query. You would do a subquery on the table to get a sum:

SELECT
  t1.Month, t1.Amount,
  SUM(SELECT t2.Amount FROM my_table t2 WHERE t2.Month <= t1.Month)
FROM my_table t1

In this way I use the table twice, once as t1 and once as t2.

edited Sep 21 '11 at 19:52

Yuck

49,664
13
105
135

answered Sep 21 '11 at 19:38

Dave

11,499
5
34
46

1

As noted in the other answers, I'd be _extremely_ reluctant to store _any_ sort of aggregated value in a table (barring OLAP needs). Especially for something like a running total - there's simply too much of a chance of anomalies. The horrors I've seen around here... – Clockwork-Muse Sep 21 '11 at 20:07
Yeah... its generally bad to store the same data more than once (ie the amounts AND their sums). It makes it difficult to stay consistent. – Dave Sep 21 '11 at 20:10
2

better to use a join than a correlated subquery. – HLGEM Sep 21 '11 at 20:18
@HLGEM [I found the opposite when answering a recent question](http://stackoverflow.com/q/7357516/73226) – Martin Smith Sep 21 '11 at 21:41

Dan J · Answer 2 · 2011-09-21T19:48:37.170

Assuming the new month and amount being inserted are represented by variables @month and @amount:

INSERT INTO t (Month, Amount, [Sum])
SELECT @month, 
       CASE WHEN @amount IS NULL THEN 0 ELSE @amount END, 
       CASE WHEN @amount IS NULL THEN SUM(Amount) ELSE SUM(Amount) + @amount END
FROM t

If the months are always going to consecutive, you could use MAX(Month) + 1 instead of @month as the inserted value.

(Though I agree with @JHolyHead's caveat that I'd be hesitant to store a running total inside the table...)

score 0 · Answer 3 · answered Sep 21 '11 at 19:37

Either store the running sum value somewhere else where you can read and update it on every transaction, or do some clever logic in a SP that calculates it during the transaction.

If I'm honest, I wouldn't store it. I'd probably prefer to calculate it in the application when I need it. That way you can filter by date/whatever other criteria you like.

t-clausen.dk · Answer 4 · 2011-09-22T07:25:02.447

Works from version 2005

;with a as(
select month, coalesce(amount, 0) amount, row_number() over(order by /*year?,*/ month) rn
from yourtable
), 
cte as
(
select month, amount, amount [sum1]
from a where rn = 1
union all
select a.month, a.amount, a.amount + b.amount
from cte join a on a.rn = cte.rn - 1
)
select month, amount, [sum1] from cte

I surgest you do not use column names like 'sum' not even for computed columns.

Don't waste a table column on a sum, imagine what happens when someone updates a column in the database. Just use a computed column in a view.

months worth of data in database table help

4 Answers4