-- 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 / tbl_STD.STD + 50.0, 1) as DEV_VAL
from TEST_RESULTS,
     summ,
    (
select sqrt(sum((cast(point as real) - avg_p) * (cast(point as real) - avg_p)) / cast(cnt as real)) as STD
from TEST_RESULTS,summ
    where TEST_ID = '100'
    ) tbl_STD
where TEST_ID = '100'
order by 3 desc, 1;