HARMEAN

Updated 2023-10-23 19:45:13.480000

Syntax

SELECT [westclintech].[wct].[HARMEAN] (
  <@x, float,>)

Description

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:

\frac{1}{H} = \frac{1}{n}\sum_{i=1}^n\frac{1}{x_i}

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 Type

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.

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

{"columns":[{"field":"HARMEAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"HARMEAN":"5.38463536083465"}]}

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.HARMEAN(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.

{"columns":[{"field":"grp"},{"field":"HARMEAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"grp":"A","HARMEAN":"18.3113064245113"},{"grp":"B","HARMEAN":"10.324454784006"},{"grp":"C","HARMEAN":"38.4566857863775"},{"grp":"D","HARMEAN":"49.2450318760875"}]}

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.

{"columns":[{"field":"grp"},{"field":"HARMEAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"grp":"C","HARMEAN":"38.4566857863775"},{"grp":"D","HARMEAN":"49.2450318760875"}]}