Problem with using aggregate functions in Subqueries

Hi,
I am currently working on implementing a group-wise z-standardization for a numeric variable.

For this, I create a subquery to calculate the mean and std. deviation for each group and plan to use this inside of a larger query with the actual values to then calculate the z-standardization.

When I run the subquery by itself it works fine but when I include it inside the larger query it gives me back the “Exception: Aggregate function STDDEV not supported”. However, the other aggregate function AVG seems to work just fine.

Could you advise on whether this is a limitation of OmniSciDB or whether this is a problem in my approach.

Thank you so much in advance!

Hi @ldnker,

I reproduced the issue and it looks like a bug or something we are missing, so it’s not a problem of your approach.

If I understand correctly you are planning to do a query like that

with subq_zstd as (select f1,avg(f2) the_avg,
                               stddev(f2) the_stddev 
                               from t1 group by f1)
select f2,f1,abs(f2-the_avg)/the_stddev
from t1 join subq_zstd on t1.f1= subq_zstd.t1

a quick hack could be to use this formula instead of the stddev built-in function into the subquery,

with subq_zstd as (select f1,AVG(f2) the_avg,
(CASE WHEN COUNT(total_amount) > 1 THEN SQRT((COUNT(total_amount) * SUM( POWER(total_amount,2)) - POWER(SUM(total_amount), 2) ) / (COUNT(total_amount)*(COUNT(total_amount) - 1))) ELSE NULL END) the_stddev

the results should be the same and work when placed into subqueries.

Another approach could be the creation of a temporary table with a CTAS statement

create temporary table subq_zstd as (select f1,AVG(f2) the_avg,
                               stddev(f2) the_stddev 
                               from t1 group by f1);

use on your query/queries and then drop.
the temporary table in omnisci is built-in memory, so leaving the session it’s dropped

I hope I understood what you wanted to achieve,
Regards,
Candido

Hi Candido,

thanks for confirming this and offering these two solutions. Yes creating a separate (temporary) table is probably the easiest for others to follow but it is great to see that one can also just create the STDDEV function from scratch.

As always this was very helpful!
Best
Frederic