This is probably not the most elegent solution, but here is something that works in 3 SQL statements using user variables.
mysql> select @gmean := 1, @ct := count(*) from g_mean; mysql> select @gmean := @gmean * test_val from g_mean; mysql> select power(@gmean, (1/@ct)) as Geo_Mean; See details below Gordon Bruce Interstate Software A MySQL US Training Partner mysql> create table g_mean (test_val float); Query OK, 0 rows affected (0.02 sec) mysql> insert into g_mean values (23),(32),(12),(145),(18); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx mysql> select avg(test_val) from g_mean; +---------------+ | avg(test_val) | +---------------+ | 46 | +---------------+ 1 row in set (0.00 sec) XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX mysql> select @gmean := 1, @ct := count(*) from g_mean; +-------------+-----------------+ | @gmean := 1 | @ct := count(*) | +-------------+-----------------+ | 1 | 5 | +-------------+-----------------+ 1 row in set (0.00 sec) mysql> select @gmean := @gmean * test_val from g_mean; +-----------------------------+ | @gmean := @gmean * test_val | +-----------------------------+ | 23 | | 736 | | 8832 | | 1280640 | | 23051520 | +-----------------------------+ 5 rows in set (0.00 sec) mysql> select power(@gmean, (1/@ct)) as Geo_Mean; +-----------+ | Geo_Mean | +-----------+ | 29.685196 | +-----------+ 1 row in set (0.02 sec) XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX > -----Original Message----- > From: Andrew Gould [mailto:[EMAIL PROTECTED]] > Sent: Saturday, May 11, 2002 7:51 AM > To: [EMAIL PROTECTED] > Subject: geometric mean aggregate function > > Has anyone out there developed a costum, aggregate > function for calculating the geometric mean of > populations? If not, would anyone be interested in > creating one? > > My development experience/skills is limited to SQL and > simple Python scripts; so I had difficulty > understanding the documentation on creating functions. > > Why _you_ might want this function: Geometric mean is > a good replacement for arithmetic mean when you want > to reduce the effects of extreme, infrequent values > without excluding the occurrences altogether. An > example is Medicare's (US government health insurance > for the elderly) use of geometric mean to derive > standard lengths of stay (days in the hospital) for > patients within Diagnostic Related Groups (DRGs). > For companies calculating statistics for internal > process improvement projects, this function would be > very useful because is describes what usually happens > better than the arithmetic mean (see below). > > Calculating geometric mean: > Geometric mean is calculated by multiplying the values > of each occurrence together and raising the resulting > product by the power of 1 divided by the sample size. > Therefore, the geometric mean of 2 3, 4, 5, and 40 > would be (2*3*4*5*40)^(1/5) = 5.448139855, which is > very different from the arithmetic mean of 10.8. I > certainly would have liked my school grades to be > calculated this way! > > If anyone has created this function and would be > willing to share it, I would greatly appreciate it. > If the developers on this list see value in adding it > as a standard function to MySQL, I think you will be > ahead of the pack as industries are becoming more data > savvy. > > Is this a fairly simple request; or would it have been > more appropriate to send this request to > [EMAIL PROTECTED]? > > Thanks, > > Andrew Gould > > __________________________________________________ > Do You Yahoo!? > LAUNCH - Your Yahoo! Music Experience > http://launch.yahoo.com > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
