-- TEST_RESULTS

with summ as (select sum(POINT)                                                                                     SUM_P,
                     avg(POINT)                                                                                     AVG_P,
                     count(*)                                                                                       CNT,
                     cast(sum(POINT * POINT) as REAL) / cast(count(*) as real) - (avg(POINT) * avg(POINT))       as V,
--         sum((point-avg(point))*(point-avg(point)))/cast(count(*) as real) as V2,
                     sqrt(cast(sum(POINT * POINT) as REAL) / cast(count(*) as real) - (avg(POINT) * avg(POINT))) as STD
              from TEST_RESULTS
              where TEST_ID = '100')

select USER_ID                                                     as USER,
       POINT                                                       as PT,
       round((cast(point as real) - AVG_P) * 10.0 / STD + 50.0, 1) as DEV_VAL
-- ,
-- sum_p,
--     avg_p,
--     v,
--     std

from TEST_RESULTS,
     summ
where TEST_ID = '100'
order by 3 desc, 1;