Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to add a running total? Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 1:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:36 AM
Points: 252, Visits: 669
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.





Post #1440010
Posted Monday, April 8, 2013 4:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Post #1440050
Posted Monday, April 8, 2013 4:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1440051
Posted Monday, April 8, 2013 4:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1440053
Posted Monday, April 8, 2013 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Post #1440056
Posted Tuesday, April 9, 2013 3:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 12, 2014 2:36 AM
Points: 252, Visits: 669
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



Post #1440240
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse