Updated: 6 August 2010
Use GEOMEAN_q to calculate the geometric mean for a dataset containing positive numbers. The equation for geometric mean is:
Syntax
SELECT [wctStatistics].[wct].[GEOMEAN_q] (
<@Values_RangeQuery, nvarchar(4000),>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine the values to be used in the GEOMEAN_q calculation.
Return Types
float
Remarks
· If any value in the dataset is less than or equal to zero, GEOMEAN_q will return an error.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #g1(
[num] [float] NOT NULL
)
insert into #g1 Values (4.6)
insert into #g1 Values (5.7)
insert into #g1 Values (8.3)
insert into #g1 Values (7.29)
insert into #g1 Values (10.965)
insert into #g1 Values (4.166667)
insert into #g1 Values (3.14159265358979)
select wct.GEOMEAN_q('SELECT num from #g1')
This produces the following result
----------------------
5.82560187538148
(1 row(s) affected)