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 GEOMEAN function

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

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**.

float

· 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.

· If you have previously used the GEOMEAN scalar function, the GEOMEAN aggregate has a different syntax. The GEOMEAN scalar function is no longer available in XLeratorDB/statistics2008, though you can still use the scalar GEOMEAN _q

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

GEOMEAN

----------------------

5.82560187538148

(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 GEOMEAN

---- ----------------------

A 22.4976063467422

B 20.6674611657522

C 50.9217866484481

D 54.0415572817856

(4 row(s) affected)

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.

grp GEOMEAN

---- ----------------------

C 50.9217866484481

D 54.0415572817856

(2 row(s) affected)