SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to add a running total?


How to add a running total?

Author
Message
Henk Schreij
Henk Schreij
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 841
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.



Evil Kraig F
Evil Kraig F
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27929 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)

Group: General Forum Members
Points: 273739 Visits: 42217
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)

Group: General Forum Members
Points: 273739 Visits: 42217
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27929 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Henk Schreij
Henk Schreij
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 841
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search