SQL Server GEOMEAN Function

Updated 2023-10-23 19:40:09.223000

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}

Syntax

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

Arguments

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

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.

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.