평균에 평균구하기 조언부탁드립니다.. 0 4 156

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 평균의 소계랑 합계부분에 평균에 평균을 구하고싶은데..어떻게하면 좋을지.. 조언부탁드립니다.. 

by 우리집아찌 [2018.09.12 12:38:53]

원하시는 결과값 과 형식을 자세히  올려주세요.


by 강석주 [2018.09.12 12:40:17]

소계는 과자 10 + 8 / 2 = 9 , 통조림 9 + 5.6 /2 = 7.3 이렇게 보여주고싶구요.. 

합계는 10+ 8 + 9 + 5.6 / 4 해서 6.15가 나왔으면합니다.. 


by 우리집아찌 [2018.09.12 13:33:35]
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)
  

 


by 강석주 [2018.09.12 14:30:23]

너무너무감사합니다...ㅠㅠㅠ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입