데이터베이스 질문과 답변
by 강석주 [2018.09.12 12:27:04]
안녕하세요 질문하나만 올리겠습니다...
상품별 갯수(CNT)의 합을 구하고,
좋아요(NICE) 의 평균을 구하려고하는데요...
평균을 보여줄때 각 상품의 NICE 평균값을 내려면 AVG로 감싸서 합계를 구하면 그만이지만..
합계 부분에 각상품의 평균의 평균을 보여주고자합니다...
WITH TEST AS (
SELECT '통조림' AS GUBUN , '참치' AS NM_PRO , '15' AS CNT , '9' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '참치' AS NM_PRO , '10' AS CNT , '' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '옥수수' AS NM_PRO , '20' AS CNT , '10' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '옥수수' AS NM_PRO , '90' AS CNT , '5' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '옥수수' AS NM_PRO , '15' AS CNT , '2' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '새우X' AS NM_PRO , '9' AS CNT , '7 ' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '새우X' AS NM_PRO , '20' AS CNT , '9' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '콘XX' AS NM_PRO , '15' AS CNT , '10' AS NICE FROM DUAL
)
SELECT GUBUN
, NM_PRO
, SUM(CNT) AS CNT
, AVG(NICE) AS NICE
FROM TEST
GROUP BY ROLLUP (GUBUN,NM_PRO)
| gubun | nm_pro | cnt | nice |
| 과자 | 콘xx | 15 | 10 |
| 과자 | 새우x | 29 | 8 |
| 과자 | 44 | 8.66666666666667 | |
| 통조림 | 참치 | 25 | 9 |
| 통조림 | 옥수수 | 125 | 5.66666666666667 |
| 통조림 | 165 | 6.5 | |
| 209 | 7.625 |
cnt 합계같은경우는 소계(빨간색)값이 44 , 165가 나오고있고 최종합계는 209가 나오고있네요..
그런데.. nice 평균의 소계랑 합계부분에 평균에 평균을 구하고싶은데..어떻게하면 좋을지.. 조언부탁드립니다..
원하시는 결과값 과 형식을 자세히 올려주세요.
소계는 과자 10 + 8 / 2 = 9 , 통조림 9 + 5.6 /2 = 7.3 이렇게 보여주고싶구요..
합계는 10+ 8 + 9 + 5.6 / 4 해서 6.15가 나왔으면합니다..
WITH TEST AS (
SELECT '통조림' AS GUBUN , '참치' AS NM_PRO , '15' AS CNT , '9' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '참치' AS NM_PRO , '10' AS CNT , '' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '옥수수' AS NM_PRO , '20' AS CNT , '10' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '옥수수' AS NM_PRO , '90' AS CNT , '5' AS NICE FROM DUAL UNION ALL
SELECT '통조림' AS GUBUN , '옥수수' AS NM_PRO , '15' AS CNT , '2' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '새우X' AS NM_PRO , '9' AS CNT , '7 ' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '새우X' AS NM_PRO , '20' AS CNT , '9' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '콘XX' AS NM_PRO , '15' AS CNT , '10' AS NICE FROM DUAL UNION ALL
SELECT '과자' AS GUBUN , '치킨X' AS NM_PRO , '5' AS CNT , '20' AS NICE FROM DUAL
)
SELECT GUBUN
, NM_PRO
, SUM(CNT) CNT
, AVG(NICE) NM_PRO
FROM (SELECT GUBUN
, NM_PRO
, SUM(CNT) AS CNT
, AVG(NICE) AS NICE
FROM TEST
GROUP BY GUBUN,NM_PRO
)
GROUP BY ROLLUP (GUBUN,NM_PRO)
너무너무감사합니다...ㅠㅠㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.