ERROR [HY000] ERROR: Aggregate function calls may not be nested */

Question

I cannot get the below line to work when adding to my query, any thoughts? stddev(stat) bias

with c as (
    SELECT A.ENGINE_ASOF_CALENDAR_DATE, A.LEVEL_1_CODE CURR, CNTR_TO_ACTIVE_RISK, A.PRICE, a.ACTIVE_WEIGHT_PCT,
           lag(a.PRICE, 1) over(partition by a.LEVEL_1_CODE order by a.ENGINE_ASOF_CALENDAR_DATE) price_lag,

           lag(CNTR_TO_ACTIVE_RISK, 1) over(partition by a.LEVEL_1_CODE order by a.ENGINE_ASOF_CALENDAR_DATE) risk_lag,

           price_lag/a.PRICE - 1 rtn,
           a.ACTIVE_WEIGHT_PCT * rtn wgt_rtn
    FROM DBS_APPL_RISK_DATAMART.USR_OWNR_RISK_DATAMART.VWC_FOREIGNEXCHANGE_FUND_EXPOSURE A
    WHERE A.PORTFOLIO_CODE = 'Sunsuper Active - SUKHH3_Active'
)

SELECT c.*,
       sum(wgt_rtn) over(partition by c.ENGINE_ASOF_CALENDAR_DATE)sum_rtn,
       sum(risk_lag) over(partition by c.ENGINE_ASOF_CALENDAR_DATE)sum_risk_lag,
       sum_risk_lag/sqrt(260) over(partition by c.ENGINE_ASOF_CALENDAR_DATE)sum_lag_risk2,
       sum_rtn/nullif(sum_lag_risk2,0) stat,
       stddev(stat) bias


FROM c

order by c.ENGINE_ASOF_CALENDAR_DATE desc

score 0 · Accepted Answer · answered May 22 '17 at 01:54

0

You are using column aliases in expressions in the same select where they are defined. You are also mixing aggregation functions in a select that has no group by.

Perhaps this will fix your problem:

WITH . . .
SELECT c.*, sum_rtn / nullif(sum_lag_risk2, 0) as stat,
       stddev(sum_rtn / nullif(sum_lag_risk2, 0)) over () as bias
FROM (SELECT c.*,
             sum(wgt_rtn) over (partition by c.ENGINE_ASOF_CALENDAR_DATE) as sum_rtn,
             sum(risk_lag) over (partition by c.ENGINE_ASOF_CALENDAR_DATE) as sum_risk_lag,
             sum_risk_lag/sqrt(260) over (partition by c.ENGINE_ASOF_CALENDAR_DATE) as sum_lag_risk2
      FROM c
     ) c
ORDER BY c.ENGINE_ASOF_CALENDAR_DATE desc

I'm not sure what the standard deviation should be partitioned by. This is over all the data.

answered May 22 '17 at 01:54

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, this works when partitioned over c.ENGINE_ASOF_CALENDAR_DATE. – Tim Cooper May 22 '17 at 02:18
Is there a way to have the standard deviation figure (bias) as a 30 day lag? – Tim Cooper May 22 '17 at 02:20
@TimCooper . . . I would suggest that you ask another question. Try to simplify the query a bit, if you are only focused on `bias`. – Gordon Linoff May 22 '17 at 02:34

ERROR [HY000] ERROR: Aggregate function calls may not be nested */

1 Answers1