Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server geometric mean function


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:

GEOMEAN function for SQL Server
 
Syntax
syntax for GEOMEAN function for SQL Server
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)



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service