GEOMEAN

Updated 2023-10-23 19:40:09.223000

Syntax

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

Description

Use the aggregate function GEOMEAN to calculate the geometric mean for a dataset containing positive numbers. The equation for geometric mean is:

G=(\prod_{i=1}^n a_i)^{1/n}

Arguments

@x

the value to be used in the geometric 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, GEOMEAN will return a NULL.

GEOMEAN is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

Examples

SELECT wct.GEOMEAN(x) as GEOMEAN

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":"GEOMEAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"GEOMEAN":"5.82560187538148"}]}

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.

{"columns":[{"field":"grp"},{"field":"GEOMEAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"grp":"A","GEOMEAN":"22.4976063467422"},{"grp":"B","GEOMEAN":"20.6674611657522"},{"grp":"C","GEOMEAN":"50.9217866484481"},{"grp":"D","GEOMEAN":"54.0415572817856"}]}

Using the same data, we only want to select those groups that have a geometric mean greater than 50.

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

HAVING wct.GEOMEAN(val) > 50;

This produces the following result.

{"columns":[{"field":"grp"},{"field":"GEOMEAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"grp":"C","GEOMEAN":"50.9217866484481"},{"grp":"D","GEOMEAN":"54.0415572817856"}]}