SELECT *
FROM
(
SELECT 'GROUP1' AS GRP, 5 AS SUM_VALUE , 1.83848332 AS VALUE FROM DUAL
UNION ALL
SELECT 'GROUP1' AS GRP, 5 AS SUM_VALUE , 3.16151668 AS VALUE FROM DUAL
)
GROUP1 , 5 , 1,83
GROUP1 , 5, 3.17
나머지 수량을 소수점 2자리 뒷에는 합산하여 큰값에 넣어주려 합니다
WITH t AS ( SELECT 'GROUP1' grp, 5 sum_value, 1.83848332 value FROM dual UNION ALL SELECT 'GROUP1', 5, 3.16151668 FROM dual ) SELECT grp, sum_value, value , TRUNC(value, 2) + DECODE(ROW_NUMBER() OVER(PARTITION BY grp ORDER BY value DESC) , 1, SUM(MOD(value, 0.01)) OVER(PARTITION BY grp), 0) AS x FROM t ;
