GEOMEAN
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:
Syntax
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 Types
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.
· 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
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
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)