데이터베이스 질문과 답변
CREATE TABLE T
AS(
SELECT '1' class ,'1' s_num, 10 score FROM dual
UNION ALL SELECT '1','2',15 FROM dual
UNION ALL SELECT '1','3',17 FROM dual
UNION ALL SELECT '2','1',20 FROM dual
UNION ALL SELECT '2','2',22 FROM dual
UNION ALL SELECT '2','3',21 FROM dual
UNION ALL SELECT '3','1',11 FROM dual
UNION ALL SELECT '3','2',19 FROM dual
UNION ALL SELECT '3','3',16 FROM dual
);
SELECT * FROM t
class s_num score 전교평균
1 1 10 16.777
1 2 15 16.777
1 3 17 16.777
2 1 20 16.777
2 2 22 16.777
2 3 21 16.777
3 1 11 16.777
3 2 19 16.777
3 3 16 16.777
위와같이 끝에 전교평균(모든 score 평균)을 계산해서 추가하고 싶은데.. 집계함수라 추가가 잘 안됩니다.. 어떻게 해야하나요??
WITH t AS
(
SELECT '1' class, '1' s_num, 10 score FROM dual
UNION ALL SELECT '1', '2', 15 FROM dual
UNION ALL SELECT '1', '3', 17 FROM dual
UNION ALL SELECT '2', '1', 20 FROM dual
UNION ALL SELECT '2', '2', 22 FROM dual
UNION ALL SELECT '2', '3', 21 FROM dual
UNION ALL SELECT '3', '1', 11 FROM dual
UNION ALL SELECT '3', '2', 19 FROM dual
UNION ALL SELECT '3', '3', 16 FROM dual
)
SELECT class, s_num, score
, ROUND(AVG(score) OVER(), 3) 전교평균
, ROUND(AVG(score) OVER(PARTITION BY class), 3) 학급평균
FROM t
;
-- 분석함수 : http://gurubee.net/lecture/2671
감사합니다 ㅠㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.