From: Peter Brawley
Date: April 5 2007 5:31am
Subject: Re: Stdev calculation for aggregrate tables
List-Archive: http://lists.mysql.com/mysql/205986
Message-Id: <4614899D.7030305@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ow Mun Heng,

If you reduce your algorithm to a fromula, I think you'll find it's 
algebraically equivalent to the formula you quote.

PB

Ow Mun Heng wrote:
> 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?
>
>
>