0

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?

Improve this question
2
  • 2
    @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". Commented Aug 9, 2012 at 1:34
  • 1
    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. :-( Commented Aug 9, 2012 at 1:38

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Can I use this as view definition? I got SQL execution error: Incorrect syntax near ‘ORDERBY'. (I replaced "myTable" with "dbo.MyTable")
ORDERBY must be written as two separate words ORDER BY. Surely you can create a view on this.
can you post your new query? be sure to change the columns with your own columnNames.
Yup ORDERBY was the issue. Rookie mistake. Thanks for the answer and being patient with me:)
No problem. You are welcome NS :)
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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.