TSQL query for "accumulated value"

Question

Possible Duplicate:
Calculate a Running Total in SqlServer

I am new to SQL. This question seems very basic but I just couldn't find the answer, maybe I am not hitting the right keyword.

In SQL server 2008 R2 I have a table like this:

Date      Value
---------------
2012-1-1    100
2012-1-2     50
2012-1-4    200

I want to create a view with accumulated value, like this:

Date      Total
---------------
2012-1-1    100
2012-1-2    150
2012-1-4    350

How should I do it?

@KenWhite, thanks for the info, it is exactly what I'm looking for. But please do not presume I didn't try searching. As a non-English speaker and new to SQL, I tried every word I could think of including "rollup", "accumulated", "historical sum", etc. I guess since I've realized this, my question should have really been "what is the terminology for [this thingy] in SQL world". — NS.X., Aug 09 '12 at 01:34
You're absolutely correct - I should not have made that presumption. I saw `running-total` in the tag, and made the assumption (and we all know what that means) that you had put it there. My apologies. :-( — Ken White, Aug 09 '12 at 01:38

score 2 · Accepted Answer · answered Aug 09 '12 at 01:24

2

This should get the running total. try this one:

SELECT dateHere,
       total,
       total + COALESCE(
                         (
                            SELECT SUM(total)
                            FROM myTable b
                            WHERE b.dateHere < a.dateHere
                          ), 0) AS RunningTotal
FROM     myTable a
ORDER BY dateHere

SQLFiddle Demo

answered Aug 09 '12 at 01:24

John Woo

258,903
69
498
492

Can I use this as view definition? I got SQL execution error: Incorrect syntax near ‘ORDERBY'. (I replaced "myTable" with "dbo.MyTable") – NS.X. Aug 09 '12 at 03:09
`ORDERBY` must be written as two separate words `ORDER BY`. Surely you can create a view on this. – John Woo Aug 09 '12 at 03:11
can you post your new query? be sure to change the columns with your own columnNames. – John Woo Aug 09 '12 at 03:13
Yup ORDERBY was the issue. Rookie mistake. Thanks for the answer and being patient with me:) – NS.X. Aug 09 '12 at 03:15
No problem. You are welcome NS :) – John Woo Aug 09 '12 at 03:16

score 1 · Answer 2 · answered Aug 09 '12 at 01:14

1

Here is one way to do it with a correlated subquery:

select t.date, t.value,
       (select sum(t2.value)
        from t t2
        where t2.date <= t.date
       ) as cumvalue
from t

answered Aug 09 '12 at 01:14

Gordon Linoff

1,242,037
58
646
786

TSQL query for "accumulated value"

2 Answers2

SQLFiddle Demo