Skip to Content

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

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 21, 2017 at 01:52 PM
    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>

    Add comment
    10|10000 characters needed characters exceeded