# Calculating standard deviation for a numeric field

I have to calculate standard deviation for a numeric field. Since there is no direct aggregate function available to calculate this in N1Ql. I am doing a join with subquery to get average of the column and then calculate standard deviation with the below formula
sqrt(sum(field value - avg value)^2/ count)

This query is taking long time to execute. Is there a better or more efficient way of calculating standard deviation of a numeric field

Below is a sample query for what I am doing

Select c.col1,
sum(power((c.col2 - a.col2_avg),2))/count(*) as col2_variance
from
(select b.col1, avg(b.col2) as col2_avg from default b group by b.col1) as a
inner join default c ON a.col1 = c.col1
group by c.col1

OR
try this

``````SELECT
c.col1,
ARRAY_SUM(ARRAY POWER(v-c.col2avg,2) FOR v IN c.aagg END)/ARRAY_COUNT(c.aagg) AS col2_variance,
FROM (SELECT b.col1, AVG(b.col2) AS col2avg,
ARRAY_AGG(CASE WHEN IS_NUMBER(b.col2) THEN b.col2 ELSE MISSING END) AS aagg
FROM default AS b
WHERE b.col1 IS NOT MISSING
GROUP BY b.col1) AS c;

CREATE INDEX ix1 ON default(col1, col2);``````