SQL Server harmonic mean function

HARMEAN

Updated: 28 February 2011

Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the HARMEAN function

Use the aggregate function HARMEAN to calculate the harmonic mean of a dataset containing positive numbers. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals. The equation for harmonic mean is:

Syntax

Arguments
@x
the value to be used in the harmonic mean calculation. @X is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
·         If any values in the dataset is less than or equal to zero, HARMEAN will return a NULL.
·         HARMEAN is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
·         If you have previously used the HARMEAN scalar function, the HARMEAN aggregate has a different syntax. The HARMEAN scalar function is no longer available in XLeratorDB/statistics2008, though you can still use the scalar HARMEAN _q
Examples
SELECT wct.HARMEAN(x) as HARMEAN
FROM (VALUES
(4.6),
(5.7),
(8.3),
(7.29),
(10.965),
(4.166667),
(3.14159265358979)
) n(x)

This produces the following result
HARMEAN
----------------------
5.38463536083465

(1 row(s) affected)

In this example, we have collected some data into 4 groups and we want to calculate the geometric mean for each group.
SELECT grp
,wct.GEOMEAN(val) as GEOMEAN
FROM (VALUES
('A',52.7349),
('A',31.0524),
('A',18.0381),
('A',10.9296),
('A',29.8941),
('A',7.9941),
('A',14.7909),
('A',12.99),
('A',33.3309),
('A',67.2309),
('B',4.2629),
('B',56.4789),
('B',3.3429),
('B',45.6525),
('B',4.4924),
('B',28.4549),
('B',106.79),
('B',35.1261),
('B',68.6844),
('B',11.75),
('C',104.1549),
('C',24.0944),
('C',58.7004),
('C',74.6604),
('C',94.1436),
('C',26.5644),
('C',34.5596),
('C',103.5516),
('C',12.2981),
('C',96.84),
('D',26.9564),
('D',105.1644),
('D',33.1101),
('D',83.0316),
('D',36.7389),
('D',83.0316),
('D',56.04),
('D',42.3141),
('D',48.5829),
('D',77.5725)
)n(grp, val)
GROUP BY grp

This produces the following result.
grp                 HARMEAN
---- ----------------------
A          18.3113064245113
B           10.324454784006
C          38.4566857863775
D          49.2450318760875

(4 row(s) affected)

Using the same data, we only want to select those groups that have a harmonic mean greater than 20.
SELECT grp
,wct.HARMEAN(val) as HARMEAN
FROM (VALUES
('A',52.7349),
('A',31.0524),
('A',18.0381),
('A',10.9296),
('A',29.8941),
('A',7.9941),
('A',14.7909),
('A',12.99),
('A',33.3309),
('A',67.2309),
('B',4.2629),
('B',56.4789),
('B',3.3429),
('B',45.6525),
('B',4.4924),
('B',28.4549),
('B',106.79),
('B',35.1261),
('B',68.6844),
('B',11.75),
('C',104.1549),
('C',24.0944),
('C',58.7004),
('C',74.6604),
('C',94.1436),
('C',26.5644),
('C',34.5596),
('C',103.5516),
('C',12.2981),
('C',96.84),
('D',26.9564),
('D',105.1644),
('D',33.1101),
('D',83.0316),
('D',36.7389),
('D',83.0316),
('D',56.04),
('D',42.3141),
('D',48.5829),
('D',77.5725)
)n(grp, val)
GROUP BY grp
HAVING wct.HARMEAN(val) > 20

This produces the following result.
grp                 HARMEAN
---- ----------------------
C          38.4566857863775
D          49.2450318760875

(2 row(s) affected)