List:General Discussion« Previous MessageNext Message »
From:Ow Mun Heng Date:April 5 2007 5:04am
Subject:Stdev calculation for aggregrate tables
View as plain text  
Hi, 

I'm looking at creating some aggregrate table based on ~2hr pull from
the main DB and looking to get standard stuffs like min/max/ave etc.

I'm having some issues with getting stdev which would be representative
of the stdev of say 10 hours of data.
>From this website : (it references using SQL Server Analysis services
but I think the concept is the same for MySQL)

http://www.phptr.com/articles/printerfriendly.asp?p=337135&rl=1

1. Calculate sum of square of each sale
2. multiple the result of step 1 by the sales count
3. sum all sales
4. Square the result of step 3
5. Substract the result of step 4 from the result of step 2
6. Multiply the sales count by one less than sales count ("sales_count"
* ("sales_count" - 1))
7. Divide the result of step 5 by the result of step 6
8. Stdev will be the square root of step 7

The results are valid (verified with actual data) but I don't understand
the logic. All the Statistical books I've read marked stdev as sqrt
(sum(x - ave(x))^2 / (n - 1). The formula is very different, hence the
confusion. 

All I know is that, it works. Only question is, why?

Can anyone explain this?

Thread
Stdev calculation for aggregrate tablesOw Mun Heng5 Apr
  • Re: Stdev calculation for aggregrate tablesPeter Brawley5 Apr