Updated: 6 August 2010
Use 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
SELECT [wctStatistics].[wct].[HARMEAN] (
<@Values_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>)
Arguments
@Values_TableName
the name, as text, of the table or view that contains the positive values to be used in the HARMEAN calculation.
@ColumnName
the name, as text, of the column in the table or view specified by @Values_TableName that contains the values to be used in the HARMEAN calculation.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Values_TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
Return Types
float
Remarks
· If any values in the dataset is less than or equal to zero, HARMEAN will return an error.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #h1(
[num] [float] NOT NULL
)
insert into #h1 Values (4.6)
insert into #h1 Values (5.7)
insert into #h1 Values (8.3)
insert into #h1 Values (7.29)
insert into #h1 Values (10.965)
insert into #h1 Values (4.166667)
insert into #h1 Values (3.14159265358979)
select wct.HARMEAN('#h1','num','', NULL)
This produces the following result
----------------------
5.38463536083465
(1 row(s) affected)