Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

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:

HARMEAN function for SQL Server
 
Syntax
XLeratorDB syntax for HARMEAN function for SQL Server
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)



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service