Cumulative average - 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 average

Author

Topic

Nixter
Starting Member

5 Posts

Posted - 2006-03-31 : 20:13:58

I have a running average of Purchases per day and the running total is done in Excel report.. I'd like to calc this in a table for SQL Reporting Services.

Here is what I use:
------------------------
SELECT PO_Date, COUNT(*) AS Requisitions, 0 AS RunningAverage
FROM PO
WHERE Created > '3/29/04'
GROUP BY PO_Date
ORDER BY PO_Date ASC
------------------------

I get three columns; Date, Count of requisitions for that day, and Average (which is all zeros).

Running an update joining by date on each column I am trying to get a running total for these PO's. Any hints? What I need to do is update column RunningAverage with a join on min(PO_Date) to current PO_Date.. At least that's what I think

If done right, I should have the following Average from the update SQL below in RunningAverage ... NOT the cleanest update SQL - I am a bit rummy from this all):
Here is some sample Code:

----------------

--Create Table
CREATE TABLE PO
(
PO_Date VarChar(20),
Requisitions INT,
RunningAverage INT
)

--Insert Values
INSERT INTO PO VALUES ('2004/03/29', 897, 0)
INSERT INTO PO VALUES ('2004/03/30', 861, 0)
INSERT INTO PO VALUES ('2004/03/31', 830, 0)
INSERT INTO PO VALUES ('2004/04/01', 716, 0)
INSERT INTO PO VALUES ('2004/04/02', 618, 0)
INSERT INTO PO VALUES ('2004/04/03', 17, 0)
INSERT INTO PO VALUES ('2004/04/04', 14, 0)
INSERT INTO PO VALUES ('2004/04/05', 746, 0)
INSERT INTO PO VALUES ('2004/04/06', 845, 0)
INSERT INTO PO VALUES ('2004/04/07', 697, 0)

--Check Values
select * from PO

--Update with averages as it should be w/ query
UPDATE PO SET RunningAverage = 879 WHERE PO_Date = '2004/03/29'
UPDATE PO SET RunningAverage = 863 WHERE PO_Date = '2004/03/30'
UPDATE PO SET RunningAverage = 826 WHERE PO_Date = '2004/03/31'
UPDATE PO SET RunningAverage = 784 WHERE PO_Date = '2004/04/01'
UPDATE PO SET RunningAverage = 657 WHERE PO_Date = '2004/04/02'
UPDATE PO SET RunningAverage = 565 WHERE PO_Date = '2004/04/03'
UPDATE PO SET RunningAverage = 587 WHERE PO_Date = '2004/04/04'
UPDATE PO SET RunningAverage = 616 WHERE PO_Date = '2004/04/05'
UPDATE PO SET RunningAverage = 624 WHERE PO_Date = '2004/04/06'
UPDATE PO SET RunningAverage = 611 WHERE PO_Date = '2004/04/07'

--Check values
select * from PO

--Drop Table PO

Thanks Much in advance!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-31 : 20:32:45

Can you explain how do you get the RunningAverage of 879 for 2004/03/29, 863 for 2004/03/30

KH
Choice is an illusion, created between those with power, and those without.

TimS
Posting Yak Master

198 Posts

Posted - 2006-03-31 : 21:24:55

Here's what I call a running average, but it is NOT the values you say are right.

UPDATE PO SET RunningAverage = NULL

DECLARE
@counter INT,
@sum INT,
@rowcount INT

SET @counter = 0; SET @sum = 0
SET @rowcount = -1 -- Set to non-zero value
WHILE @rowcount <> 0
BEGIN
UPDATE p SET
@sum = @sum + Requisitions, @counter = @counter + 1,
p.RunningAverage = @sum/@counter
FROM PO p
WHERE p.RunningAverage IS NULL AND p.PO_Date =
(SELECT MIN(p2.PO_Date) FROM PO p2 WHERE p2.RunningAverage IS NULL)
SELECT @rowcount = @@ROWCOUNT
END

Tim S

Nixter
Starting Member

5 Posts

Posted - 2006-04-01 : 11:09:58

TimS & khtan - Thank you both for your responses.. I just tried this and IT WORKS (insert applause and much bowing). I *did* catch a math mistake (like I said - I was code-zombied!) and THIS is the corrected update (I calc'd these values in Excel, ):

UPDATE PO SET RunningAverage = 897 WHERE PO_Date = '2004/03/29'
UPDATE PO SET RunningAverage = 879 WHERE PO_Date = '2004/03/30'
UPDATE PO SET RunningAverage = 862 WHERE PO_Date = '2004/03/31'
UPDATE PO SET RunningAverage = 826 WHERE PO_Date = '2004/04/01'
UPDATE PO SET RunningAverage = 784 WHERE PO_Date = '2004/04/02'
UPDATE PO SET RunningAverage = 656 WHERE PO_Date = '2004/04/03'
UPDATE PO SET RunningAverage = 564 WHERE PO_Date = '2004/04/04'
UPDATE PO SET RunningAverage = 587 WHERE PO_Date = '2004/04/05'
UPDATE PO SET RunningAverage = 616 WHERE PO_Date = '2004/04/06'
UPDATE PO SET RunningAverage = 624 WHERE PO_Date = '2004/04/07'

Thus after running this in production the numbers match spot on - there may be some rounding down vs. up on a few numbers in the future - but that's fine - it's just an average and as long as we are within one all is good.

Many Many Many thanks!! - you folks ROCK!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-04-11 : 08:26:19

An article about that kind of problem:

http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 09:09:14

If you use reports like Crystal Reports, make use of its running total features

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources