데이터베이스 질문과 답변
WITH T AS (
SELECT 13000 as AMT FROM DUAL UNION ALL
SELECT 14000 as AMT FROM DUAL UNION ALL
SELECT 15000 as AMT FROM DUAL UNION ALL
SELECT 13000 as AMT FROM DUAL UNION ALL
SELECT 13000 as AMT FROM DUAL UNION ALL
SELECT 14002 as AMT FROM DUAL UNION ALL
SELECT 15001 as AMT FROM DUAL
)
SELECT TOT_AMT
, TRUNC(TOT_AMT / CNT, 0) as DIV_AMT
FROM (SELECT SUM(AMT) OVER() as TOT_AMT
, COUNT(*) OVER() as CNT
FROM T)
;
/*
TOT_AMT DIV_AMT
97003 13857
97003 13857
97003 13857
97003 13857
97003 13857
97003 13857
97003 13857
*/
여기서 마지막 ROW에는 나머지값을 포함한 값으로 보정하고 싶습니다.
즉, 아래와 같이 결과가
/* TOT_AMT DIV_AMT 97003 13857 97003 13857 97003 13857 97003 13857 97003 13857 97003 13857 97003 13861 */ 나오게 하고싶습니다.
DB는 oracle입니다.
조언 부탁드립니다.
감사합니다.
by 마농
[2021.03.15 10:11:44]
WITH t AS
(
SELECT 13000 amt FROM dual
UNION ALL SELECT 14000 FROM dual
UNION ALL SELECT 15000 FROM dual
UNION ALL SELECT 13000 FROM dual
UNION ALL SELECT 13000 FROM dual
UNION ALL SELECT 14002 FROM dual
UNION ALL SELECT 15001 FROM dual
)
SELECT tot_amt
, FLOOR(tot_amt / cnt)
+ DECODE(ROWNUM, cnt, tot_amt - FLOOR(tot_amt / cnt) * cnt, 0) div_amt
FROM (SELECT SUM(amt) OVER() tot_amt
, COUNT(*) OVER() cnt
FROM t
)
;
by 짱구
[2021.03.15 10:14:24]
감사합니다.
저에게 딱 맞는 해결법입니다. ^^
) 버튼을 클릭하여 작성 하시면 됩니다.