SAP HANA SQL: avg and stddev are zero if count is used

[Former Member]

The following code works as expected:

SELECT "FE_SPEC" 
    ,"Maturity_Aggregation"
    , "Rating_Aggregation"
    -- , count(*) as N -- This resulst in avg, stddev = 0 !!
    , avg("Spread_Over_Yield") "AvgVal"
    , stddev("Spread_Over_Yield") as "Sigma"
 FROM "_SYS_BIC"."package/VIEW" 
 WHERE "Timestamp" = '2016-10-05 02:30:12.0000000' 
     AND "JobId" = 'D595B5B0-293E-4A64-9765-9BAE60DD9BD3'
     AND "Spread_Over_Yield" <> 0 
     and fe_spec = 'AmortBond' and "Maturity_Aggregation" = '001' and "Rating_Aggregation" = 'Investment Grade'
GROUP BY "FE_SPEC" ,"Maturity_Aggregation", "Rating_Aggregation"

However when I add a count to the statement then

- count shows the correct number: 2

- avg and stddev are 0 :

SELECT "FE_SPEC" 
    ,"Maturity_Aggregation"
    , "Rating_Aggregation"
    , count("Spread_Over_Yield") N
    , avg("Spread_Over_Yield") "AvgVal"
    , stddev("Spread_Over_Yield") as "Sigma"
 FROM "_SYS_BIC"."package/VIEW" 
 WHERE "Timestamp" = '2016-10-05 02:30:12.0000000' 
     AND "JobId" = 'D595B5B0-293E-4A64-9765-9BAE60DD9BD3'
     AND "Spread_Over_Yield" <> 0
     and fe_spec = 'AmortBond' and "Maturity_Aggregation" = '001' and "Rating_Aggregation" = 'Investment Grade'
 GROUP BY "FE_SPEC" ,"Maturity_Aggregation", "Rating_Aggregation"

The raw Data is:

Is it a bug?

Or how to fix this?

[Former Member]

Fortunately I already found a work-around: Put count in the last column

:

SELECT "FE_SPEC" 
    ,"Maturity_Aggregation"
    , "Rating_Aggregation"
    , avg("Spread_Over_Yield") "AvgVal"
    , stddev("Spread_Over_Yield") as "Sigma"
    , count("Spread_Over_Yield") N
 FROM "_SYS_BIC"."package/VIEW" 
 WHERE "Timestamp" = '2016-10-05 02:30:12.0000000' 
     AND "JobId" = 'D595B5B0-293E-4A64-9765-9BAE60DD9BD3'
     AND "Spread_Over_Yield" <> 0
     and fe_spec = 'AmortBond' and "Maturity_Aggregation" = '001' and "Rating_Aggregation" = 'Investment Grade'
 GROUP BY "FE_SPEC" ,"Maturity_Aggregation", "Rating_Aggregation"<br>

[lbreddemann]

I tried to reproduce this with your data in a column table and wasn't able to create the effect (HANA 1 SP12)

Also, the order of columns really shouldn't play any role in a relational query environment,

That leaves me with the impression that you're looking at the effect of the non-relational behaviour of calculation views.

For further analysis of this specific problem, I suggest opening a support incident.

[Former Member]

Thanks for the comment Lars.

I thought about the "non-relational behaviour" too.

So I'll open an incident.