How toCalcuate Avg and StdDev using #Temp Table

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 2005 Forums

Transact-SQL (2005)

How toCalcuate Avg and StdDev using #Temp Table

Author

Topic

kka_anand
Starting Member

24 Posts

Posted - 2011-03-06 : 14:27:42

Hi All,

I have a temporary table with price values. Based on the price, average and standard deviation should be calculated and updated on to the temp table. I am finding it difficult to calculate both Avg and StdDev. Can anyone help me out on this.

CREATE TABLE #TempTbl(
Price Float,
Avrge Float,
StdDev Float
)
INSERT INTO #TempTbl (Price) VALUES (4.30);
INSERT INTO #TempTbl (Price) VALUES (4.15);
INSERT INTO #TempTbl (Price) VALUES (4.30);
INSERT INTO #TempTbl (Price) VALUES (4.50);
INSERT INTO #TempTbl (Price) VALUES (4.50);

SELECT * FROM #TempTbl

Result
######
4.30 NULL NULL
4.15 NULL NULL
4.30 NULL NULL
4.50 NULL NULL
4.50 NULL NULL

--DROP TABLE #TempTbl

FinalResult should be in the below format
***************************************************************

#TempTbl
========
Price Avrge StdDev
-----------------------
4.30 --
4.15 4.30 --
4.30 4.23 0.11
4.50 4.25 0.09
4.50 4.31 0.14

Thanks n Advance
Anand

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-06 : 17:51:23

Looks like you are trying to calculate the avg and std dev of all the rows above the current row. To do this, there is one vital piece of information missing: how are the rows ordered?

It may seem like they should be ordered in the order in which you are inserting them into the table, but in a database table, there is no such guarantee. It doesn't care what order you inserted them in; it might store and return the data to you in any order it pleases unless you explicitly specify the ordering scheme in your query.

So you must figure out a way to specify the order. For example, if you say that they should be ordered in the same order as you insert them, then we could do the following:

CREATE TABLE #TempTbl(
SequenceId int identity(1,1),
Price Float,
Avrge Float,
StdDev Float
)
INSERT INTO #TempTbl (Price) VALUES (4.30); 
INSERT INTO #TempTbl (Price) VALUES (4.15);
INSERT INTO #TempTbl (Price) VALUES (4.30); 
INSERT INTO #TempTbl (Price) VALUES (4.50); 
INSERT INTO #TempTbl (Price) VALUES (4.50);

Now you can use the SequenceId to order the rows, and if you can do that, doing the calculations is easy.

One thing I would caution though is to not save those calculated values into the table. The reason is that if you were to change the price in any row or delete any row, then you will have to redo the calculations and repopulate the table.

With that caveat, here is one way to do the computations:

select
   Price,
  Avrge,
  StdDev
from
  #TempTbl t
  cross apply
  (
     select avg(r.Price) as Avrge, stdev(r.Price) StdDev
     from #TempTbl r
     where r.SequenceId < t.SequenceId -- See! Couldn't do this if I didn't know how the rows are to be ordered.
  )rc
order by SequenceId -- if I don't specify the order here, no guarantee how the output will be ordered

BTW, AVG and STDDEV are standard MS-SQL functions. You may want to look up STDDEV and STDDEVP and decide which one you want. First is non-biased (and then the other has to be biased, right? :--). I know nothing about all that stuff, so I will say no more. You might want to refer to MSDN or some math reference site (mathworld.wolfram.com is my favorite) to figure out which one you want.

Hope the long-winded reply helps. I was in a chatty mood, hence the long reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources