23.8. Harmonic Mean and Geometric Mean

The harmonic mean is defined as the reciprocal of the arithmetic mean of the reciprocals of the values of a set. It is appropriate when dealing with rates and prices. Of limited use, it is found mostly in averaging rates.

SELECT COUNT(*)/SUM(1.0/x) AS harmonic_mean
 FROM Foobar;

The geometric mean is the exponential of the mean of the logs of the data items. You can also express it as the nth root of the product of the (n) data items. This second form is more subject to rounding errors than the first. The geometric mean is sometimes a better measure of central tendency than the simple arithmetic mean when you are analyzing change over time.

SELECT EXP (AVG (LOG (nbr))) AS geometric_mean FROM NumberTable; ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.