GEOMEAN_q

Updated 2023-10-23 19:41:23.560000

Syntax

SELECT [westclintech].[wct].[GEOMEAN_q] (
   <@Values_RangeQuery, nvarchar(4000),>)

Description

Use the scalar function GEOMEAN_q 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}

Arguments

@Values_RangeQuery

the select statement, as text, used to determine the values to be used in the GEOMEAN_q calculation.

Return Type

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

{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"5.82560187538148"}]}