Cumulative Totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cumulative Totals

Author

Topic

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-05 : 05:59:57

Hi folks..

I've got the following test table..

create table testtable
( customer int null, week int null, status varchar (4) null, contribution int null, coll numeric null)

And data like this

INSERT INTO testtable SELECT 1,1,'NEW',10,1
INSERT INTO testtable SELECT 1,2,'ACT',5,0.75
INSERT INTO testtable SELECT 1,3,'ACT',5,0.67
INSERT INTO testtable SELECT 1,4,'ACT',0,0.5
INSERT INTO testtable SELECT 1,5,'WOF',0,0.4
INSERT INTO testtable SELECT 2,1,'NEW',20,1
INSERT INTO testtable SELECT 2,2,'ACT',15,0.875
INSERT INTO testtable SELECT 2,3,'ACT',15,0.83
INSERT INTO testtable SELECT 2,4,'ACT',20,0.875
INSERT INTO testtable SELECT 2,5,'ACT',20,0.9
INSERT INTO testtable SELECT 2,6,'PU',20,0.9167
INSERT INTO testtable SELECT 2,9,'PUR',30,1
INSERT INTO testtable SELECT 2,10,'ACT',30,1
INSERT INTO testtable SELECT 3,1,'NEW',15,1
INSERT INTO testtable SELECT 3,2,'ACT',15,1
INSERT INTO testtable SELECT 3,3,'ACT',15,1
INSERT INTO testtable SELECT 3,4,'ACT',15,1
INSERT INTO testtable SELECT 3,5,'ACT',15,1
INSERT INTO testtable SELECT 3,6,'ACT',15,1
INSERT INTO testtable SELECT 3,7,'ACT',15,1
INSERT INTO testtable SELECT 3,8,'ACT',15,1
INSERT INTO testtable SELECT 3,9,'ACT',15,1
INSERT INTO testtable SELECT 3,10,'PU',15,1
INSERT INTO testtable SELECT 4,1,'NEW',20,1
INSERT INTO testtable SELECT 4,2,'ACT',10,0.75
INSERT INTO testtable SELECT 4,3,'ACT',0,0.5
INSERT INTO testtable SELECT 4,4,'ACT',20,0.625
INSERT INTO testtable SELECT 4,5,'ACT',0,0.5
INSERT INTO testtable SELECT 4,6,'ACT',0,0.4167
INSERT INTO testtable SELECT 4,7,'ACT',0,0.3571
INSERT INTO testtable SELECT 4,8,'ACT',0,0.3125
INSERT INTO testtable SELECT 4,9,'WOF',0,0.2778
INSERT INTO testtable SELECT 5,1,'NEW',20,1
INSERT INTO testtable SELECT 5,2,'ACT',40,1
INSERT INTO testtable SELECT 5,3,'ACT',40,1
INSERT INTO testtable SELECT 5,4,'PU',40,1

What is the best (most efficient) method for producing a cumulative total field by week?

The best I've found from searching here seems to be

select
customer, [week], status, contribution, coll, 0 as [cum contrib]
into
cum_testtable
from testtable with (nolock)

create clustered index forceweekorder on cum_testtable ([week])

select * from cum_testtable

declare @sumtotal int
set @sumtotal = 0

update
cum_testtable
set @sumtotal = [cum contrib] = [contribution] + @sumtotal

But I'm not sure if -

Using the clustered index will guarantee that the total is worked correctly by week and

That this solution is scalable. It's fine for my 36 row test table, but the real results will come from a table which has some 400 million rows in it.

-------
Moo.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-05 : 06:06:07

Hi

Have you had a look at this article ? It might give you some hints

http://www.sqlteam.com/item.asp?ItemID=3856

Damian

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-05 : 06:10:12

Indeed yes it did. I guess what I was really after was if anyone had had any real life experiences of using the concepts in the article for fairly large volumes of data,so I can use the faster method without waiting to test the others.

-------
Moo.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-05 : 07:14:31

quote:

create clustered index forceweekorder on cum_testtable ([week])

select * from cum_testtable

declare @sumtotal int
set @sumtotal = 0

update
cum_testtable
set @sumtotal = [cum contrib] = [contribution] + @sumtotal

This tends to be right, but doesn't have to be. Just because there is a clustered index on [week], doesn't necessarily mean that the update will put everything in the right order.

I think the article Damian mentioned pretty clearly says the cursor is the fastest ... it shouldn't be too hard to test all three though, even with a large data set.

EDIT: ... plus, why do you want to store the total? seems to me that it could easily lead to update/insert anomolies ...

Jay White
{0}

Edited by - Page47 on 06/05/2003 07:16:05

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-05 : 08:38:53

Hi. I might try them all then. Everything seems to take an age on these servers though. :/

I'm not particularly interested in storing the cumulative total, but the initial figures have more operations performed on them which means I need to keep the cumulative total somewhere until the rest of the data is grouped, summed up etc.

-------
Moo.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-05 : 09:43:38

quote:

I think the article Damian mentioned pretty clearly says the cursor is the fastest ... it shouldn't be too hard to test all three though, even with a large data set.

But Garth was testing the cursor against a subquery and a join, not a temporary table update.

You can at least detect when the update method goes wrong by counting the number of times the expected row order is violated -- though it gets messy when there are multiple columns in the order.

Edited by - Arnold Fribble on 06/05/2003 09:48:04

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-05 : 09:50:07

I'm also not sure if it's better to use a temporary table or a (non temporary) table for a large data set.

Is there any real difference?

My assumption is that a "real" table will be better than a temporary table.

-------
Moo.

spromtet
Starting Member

7 Posts

Posted - 2003-06-05 : 14:54:37

I'm not sure if you'd tried this or even if this will work for you o, but have you tried the WITH ROLLUP or WITH CUBE options?

SELECT customer, week, sum(contribution)
FROM
testtable
GROUP BY customer, week
WITH ROLLUP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-06 : 07:57:41

Hey Mr Mist -- why do you need running totals? For further calculations, or just to present to the end user?

If it is for further calculations (i.e., calculating something for each row up to a ceiling) then by all means do it in SQL.

But if not, do it at the presentation layer ... MUCH easier for almost all tools.

Crystal/Access -- just create a running total field
ASP -- just keep a variable with the running total and reset at every group
..etc...

I understand the need for running totals in SQL (I have done this quite a few times, with benefit calucations for budgeting and all that) but 9 times out of 10 it is just to put on a piece of paper or on a screen so users can VIEW it, and if that is the case don't force SQL Server to do needless calculations when a simple tweak to the presentation layer is all that is required.

- Jeff

Edited by - jsmith8858 on 06/06/2003 07:58:10

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-06 : 09:08:27

Oh I totally agree. The argument given to me was that these figures are the basis for a number of different presentation things, and the idea is that they are done just once instead of many times. I have relatively little input into it.

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources