데이터베이스 질문과 답변

ORACLE 에서 sub-total, total 가져올때 row수를 같이 가져올수있는방법이 있을까요? 0 2 2,031

by 자바천재 [2014.09.25 13:46:13]

Country Project type Total 14-Sep 14-Oct 14-Nov
country_a project_a a 3 1 1 1
country_a project_a Sub-Total 3 1 1 1
country_a project_b a 27 9 9 9
country_a project_b Sub-Total 27 9 9 9
country_a Sub-Total a 30 10 10 10
country_a Sub-Total Sub-Total 30 10 10 10
country_b project_c a 7 3 2 2
country_b project_c Sub-Total 7 3 2 2
country_b Sub-Total a 7 3 2 2
country_b Sub-Total Sub-Total 7 3 2 2
country_c project_d b 9 3 3 3
country_c project_d Sub-Total 9 3 3 3
country_c project_e a 5 1 2 2
country_c project_e b 3 1 1 1
country_c project_e Sub-Total 8 2 3 3
country_c Sub-Total a 5 1 2 2
country_c Sub-Total b 12 4 4 4
country_c Sub-Total Sub-Total 17 5 6 6
country_d project_f a 6 2 2 2
country_d project_f Sub-Total 6 2 2 2
country_d Sub-Total a 6 2 2 2
country_d Sub-Total Sub-Total 6 2 2 2
country_e project_g a 4 2 1 1
country_e project_g Sub-Total 4 2 1 1
country_e Sub-Total a 4 2 1 1
country_e Sub-Total Sub-Total 4 2 1 1
Total Sub-Total a 52 18 17 17
Total Sub-Total b 12 4 4 4
Total Sub-Total Sub-Total 64 22 21 21

 

상기처럼 데이터가 나옵니다

그런데

queyr는 하기와 같은식으로 했습니다

그런데 이때 상기표에서 보시면 country는 6개가 같죠?, project 는 2개가 초기에 같습니다 이것을 각각의 row에서 알수 있는 방법이 있을까요?

with t1 as (
    select 'country_a' as country, 1 country_order, 'project_a' as project, 1 project_order, 'a' type, 1 type_order, 1 as one, 1 as two, 1 as three from dual union all
    select 'country_a' as country, 1 country_order, 'project_b' as project, 2 project_order, 'a' type, 1 type_order,  1 as one, 1 as two, 1 as three from dual union all
    select 'country_b' as country, 2 country_order, 'project_c' as project, 1 project_order, 'a' type, 1 type_order,  1 as one, 1 as two, 1 as three from dual union all
    select 'country_c' as country, 3 country_order, 'project_d' as project, 1 project_order, 'a' type, 1 type_order,  1 as one, 1 as two, 1 as three from dual
)
  SELECT  NVL(T1.country, 'Total') country
                            , NVL(T1.project, 'Sub-Total') project
                            , NVL(T1.type, 'Sub-Total') type
                            ,  sum(one) + sum(two) + sum(three)  AS TOTALMM
                            ,sum(one) one
                             ,sum(two) two
                           ,  sum(three) three
                FROM T1               
              GROUP BY CUBE ( (T1.country_order, T1.country)
                            , (T1.project_ORDER, T1.project)
                            , (T1.TYPE_ORDER, T1.type)
                            )
             HAVING GROUPING_ID(T1.country_order, T1.project_order, T1.TYPE_ORDER)
                    NOT IN (4, 5)
             AND sum(one + two + three)  > 0
              ORDER BY T1.country_order
                  , T1.project_ORDER
                  , T1.TYPE_ORDER 

by 마농 [2014.09.25 14:35:01] 
SELECT country
     , project
     , type
     , totalmm
     , one
     , two
     , three
     , COUNT(*) OVER(PARTITION BY country_order) country_cnt
     , COUNT(*) OVER(PARTITION BY country_order, project_order) project_cnt
  FROM (SELECT country_order, project_order, type_order
             , NVL(country, 'Total'    ) country
             , NVL(project, 'Sub-Total') project
             , NVL(type   , 'Sub-Total') type
             , SUM(one + two + three) totalmm
             , SUM(one) one
             , SUM(two) two
             , SUM(three) three
          FROM t1
         WHERE one + two + three > 0
         GROUP BY CUBE( (country_order, country)
                      , (project_order, project)
                      , (type_order   , type   )
                      )
        HAVING GROUPING_ID(country_order, project_order, type_order) NOT IN (4, 5)
           AND SUM(one + two + three) > 0
        )
 ORDER BY country_order, project_order, type_order
;

 

by 자바천재 [2014.09.25 16:46:07]

정말 감사합니다

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