Running totals (better version).

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

SQL Server Development (2000)

Running totals (better version).

Author

Topic

Crespo

85 Posts

Posted - 2002-11-25 : 11:28:58

Hi,

I was wondering if somebody could suggest an intersting algorithm for computing running totals.

Suppose you have

KEY|inv|total
01|234|234
02|984|1218
03|783|2001
04|76|2077
05|87|2164

THe running total is displayed in the last column. Now I know what some are thinking... easy right?
something like,
SELECT KEY, INV, SUM(INV) AS TOTAL
FROM n00bs A
CROSS JOIN n00bs B
WHERE A.KEY = B.KEY
AND A.KEY <= B.KEY
bla bla bla
GROUP BY bla bla bla A.INV
ORDER BY KEY

this of course depends on ordering the data, but is there a better way of doing this?
Performance is an issue here... how well would this methid do if say we have 200000+ records?!!!!

Thanks!

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-25 : 11:31:37

Have you read Garth's article on running totals?

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

There are several approaches listed and compared.

Crespo

85 Posts

Posted - 2002-11-25 : 11:33:10

quote:

Have you read Garth's article on running totals?

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

There are several approaches listed and compared.

SSSHHHHIII.... SUGAR!... .man... I always forget to do a quick search for articles... sorry to waste your time.

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-25 : 11:35:40

No problem.

Crespo

85 Posts

Posted - 2002-11-25 : 11:40:53

quote:

Have you read Garth's article on running totals?

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

There are several approaches listed and compared.

Hmmmm... read the article but not completely happy.... I think his second solution is similar to the one that I've already come up with. Hmmm.... thanks for the link though... I would like to read it a bit more when I have some spare time tomorrow.

Best Regards.

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-25 : 13:53:10

quote:

Have you read Garth's article on running totals?

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

Rob,

I read the article, and may have found a mistake

In the 3rd query: "AS Runningtotal" is in the wrong place??

Should it be

SELECT a.DayCount,
a.Sales,
SUM(b.Sales)AS RunningTotal
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount)
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales

Am I wrong?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-25 : 17:55:33

I usually like to alias my WHERE clauses also.

I think you're right, SamC, it was in the wrong place.

And, as far as I know, that is the best way to do running totals. However, if you are writing a report using Access or Crystal reports, use the report designer's "running totals" feature on a field to give you running totals. Sometimes people try to do too much with queries when the end result is only a report!

Of course, if you are using the running totals in calculations (I have done this, for salary benefits up to a salary ceiling per emp per year) then you would have to calculate this values in a SQL statement.

Finally, even if you have 200,000 records in your table, if you have just 100 or so GROUP BY's in your running total query, and you properly filter it by the correct date ranges and key fields each time you query, you should have no problem with a table that size.

Each time you have a GROUP in your subtotal, it lowers the number of records SQL must evaluate. That is, for 200,000 records, if it is all records and a running sum, for record #200,000 SQL must add up 200,000 records. But with say 100 equal-size groups, the most it must ever add up for any 1 record is 2,000 records, which is not too shabby.

That is, if N = # of groups, and R = # of records, and we assume each group in the recordset is about the same size (R/N), we get:

# of SUMS = SUM(1..R/N) * N.

where SUM(1..R/N) means the SUM of 1 + 2 + 3 + .. (R/N).

And of course, SUM(1..R/N) = (R/N * (R/N + 1))/2 (from my calculus days).

So, with 200,000 records and no groups, total SUMS =
20,000,100,000.

BUT, with 100 groups, total SUMS = 200,100,000.

And with 200 groups, total SUMS = 50,050,000.

which is 100 - 400 times less calculations than the orginal. (Still alot, though!)

I just wanted to show that the total record count isn't the only factor in writing queries like this one. And I had some time on my hands!

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-25 : 18:29:52

Garth's article doesn't cover teh quickest method

--set up the table and values
declare @tab table (val int, cum int)
insert into @tab (val) values (3)
insert into @tab (val) values (3)
insert into @tab (val) values (3)
insert into @tab (val) values (3)
insert into @tab (val) values (3)
insert into @tab (val) values (3)

--cumulative variable
declare @cum int
set @cum = 0

--calc cumulative values

update @tab
set @cum = cum = @cum + val

--and show results
select * from @tab

only one pass through the data - I call it - the "robvolk" solution....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources