# Calculating Geometric Mean of values in a column using SQL

To calculate the Geometric Mean, you have to first multiply all values and then take the nth root of that number, where n is the number of values in the column. There is no function in SQL to calculate the geometric mean. However the following recursive CTE can be used to calculate the Geometric Mean of a column of data.

## Recursive CTE SQL

```
with recursive test_data as (
SELECT 1 AS col1
UNION ALL
SELECT 3
UNION ALL
SELECT 5
)
, numbers_to_multiply as (
SELECT array_agg(col1) as number_array
FROM test_data
)
, multiply as (
select
number_array[0] as multiplied
, 1 as array_index
, number_array
from numbers_to_multiply
union all
select
multiplied * number_array[array_index]
, array_index + 1
, number_array
from multiply where array_index < array_length(number_array)
)
select power(multiplied, 1/array_index) as geometric_mean
from multiply
order by array_index desc limit 1;
```

### Query output

geometric_mean |
---|

2.46621207433047 |