How to add a running total?

Question

How to add a running total?

Henk Schreij

SSCarpal Tunnel

Points: 4554
More actions
April 8, 2013 at 1:52 pm

#146044

I need a running total on a table with two fields (Year and Count of Orders in that year).
E.g. (using Orders from Northwind):
Yr Ct
1996 152
1997 408
1998 270
SELECT YEAR(OrderDate)AS Yr, COUNT(*) AS Ct FROM Orders
GROUP BY YEAR(OrderDate)
ORDER BY YEAR(OrderDate)
This is what I need:
Yr Ct Total
1996 152 152
1997 408 560
1998 270 830
Using SqlServer 2005, is a "set based" solution possible (without using a cursor)?
It is a small table, so the query doesn't need to be efficient. A simple solution will do.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Running totals are best done in reporting environments. SSRS handles this cleanly.

If you HAVE to get your procedure/T-SQL to do this, look up the article on this site under 'Quirky Update'. There's other information out there also under the nickname 'Serial Update', and a few others. Running Totals in SQL is another set of keywords.

The Quirky Update article (and ensuing conversations) have some of the tightest, nearly foolproof code to get T-SQL to perform this task... because it's impossible to foolproof it without using a loop otherwise.

Where is the final destination for this data? If it's a reporting environment, I recommend you let them do the Running Total... just for sanity.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

There are many so-called "set-based" solutions for Running Totals. One of the most common can be found in the following article which explains why it shouldn't be used.

http://www.sqlservercentral.com/articles/T-SQL/61539/

Other than the new windowing functionality for SUM offered in SQL Server 2012, all "set based" methods except the "Quirky Update" method and the multi-pass UPDATE method will usually get worse at an exponential rate as rowcounts increase. The multi-pass UPDATE method can also be worse than a cursor if the data doesn't have "groupings" in it. The "Quirky Update" method will beat even the new windowing functions in SQL Server 2012 but it's an undocumented feature that many fear to use. One of the problems with it is that it's not a single query. It requires some careful planning with variables and indexes and a check must be built in to make sure it reports an error if it "goes haywire".

Since you have so few rows, my recommendation would be to avoid all of that and write a nice "firehose" (forward only, read only, static) cursor to solve your running total problem. In SQL versions less than 2012, it's the second fastest, second least resource intensive method there is.

If you end up with a million rows, post back and I'll show you how to do the Quirky Update properly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Heh... Craig, you must've beat by post by seconds. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Jeff Moden (4/8/2013)
Heh... Craig, you must've beat by post by seconds. 🙂

Milliseconds, most likely. See what happens when you get so thorough in your answers? 😉

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 5

Jeff and Craig, thank you for answering my question.

@Craig: The suggestion of using a report is not possible, I am sorry.

I made a report function in my Delphi program, in which I can place a Sql-instruction.

When they push a button it produces an Excel result.

@jeff: The quirky update solution is to difficult for this use, I am sorry again.

The user wants a list of the amount of bookings per week.

I use the excellent isoweek solution of Jeff (I am so happy with this publication).

There are not so much bookings in a year (a max of 5000), and after a year al bookings are written to a history file and it starts again from zero.

A simple COUNT(*) and a GROUP BY IsoWeek, gives the user the most recent bookings per week.

Knowing that adding a running total is difficult, I'll only add a grand total, that's what the user gets.

Thank you for your effort