Mailbox-Line: From pgman@candle.pha.pa.us  Mon Jun 24 14:34:28 2002
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200206241616.g5OGGa504236@candle.pha.pa.us>
Subject: Re: variance aggregate function incorrect?  Reference Materials
In-Reply-To: <1024956806.2590.22.camel@Askr>
To: Joseph Syjuco <joseph@asti.dost.gov.ph>
Date: Mon, 24 Jun 2002 12:16:36 -0400 (EDT)
Cc: postgres <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

Joseph Syjuco wrote:
> hi
> i needed the variance function ... i dont know if i introduced the wrong
> parameters or maybe this variance is not the variance that im looking
> for but it doesnt provide the right results
> 
> variance=(nEx^2 - (Ex)^2)/(n(n-1))
> my sql statement
> select variance(answer) from tbl_answer (where answer is of type
> integer)
> 
> because of this i need to create my own variance aggregate (unless
> someone enlightens me on whats happening with the variance aggregate) so
> i would like to know if theres anyone who knows a good create aggregate
> tutorial (with samples) ... all i got from google so far is the
> reference from ninthwonder.com

Uh, I tried this:

	test=> select variance(relnatts) from pg_class;
	   variance    
	---------------
	 24.4259169884
	(1 row)

Not sure if it is the right number or not.  Have you tried taking the
square root and see if that number is correct.

The docs say:

       The variance is the square of the standard deviation.  The
       supported data types and result types are the same as for
       standard deviation.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026