Q&A

  • 오라클 ROLLUP에 대해...
select clcd,clnm,year,mnth,sum(cgmt) 청구금액,sum(wjmt) 외주금액
from
(select ga_clcd clcd,ad_clnt clnm, ga_year year,ga_mnth mnth,
       ga_cgmt cgmt,ga_wjmt wjmt
from gamsdb, splydb
where ga_year = '2004' and ga_mnth >= '01' and ga_mnth <= '04'
  and ga_clcd in ('1010','1020')
  and ga_clcd = ad_keys(+)
)
group by rollup(clcd,clnm,year,mnth)

===========결과====================================
clcd    clnm   year mnth  청구금액      외주금액
1010  A 회사   2004  01  1433269230   1778377939    
1010  A 회사   2004  02  1816508927   2796313523    
1010  A 회사   2004  03  1126228172   1846868913    
1010  A 회사   2004  04  260259300    263120580    
1010  A 회사   2004       4636265629   6684680955    
1010  A 회사                4636265629   6684680955    
1010                           4636265629   6684680955    
1020  B 회사   2004  01  148820000    125892000    
1020  B 회사   2004  02  73531000     62661000      
1020  B 회사   2004  03  209899000    174468000    
1020  B 회사   2004  04  234595000    61606950      
1020  B 회사   2004       666845000    424627950    
1020  B 회사                666845000    424627950    
1020                           666845000    424627950    
                                 5303110629   7109308905

소계에 해당하는 부분이 세줄이나 나타났네요...쩝

====================================================
이것을 아래처럼 나타내고 싶습니다.

clcd    clnm       year mnth  청구금액      외주금액
1010  A 회사   2004  01  1433269230       1778377939    
1010  A 회사   2004  02  1816508927       2796313523    
1010  A 회사   2004  03  1126228172       1846868913    
1010  A 회사   2004  04  260259300        263120580    
1010   회사계               4636265629       6684680955    
1020  B 회사   2004  01  148820000        125892000    
1020  B 회사   2004  02  73531000         62661000      
1020  B 회사   2004  03  209899000        174468000    
1020  B 회사   2004  04  234595000        61606950      
1020   회사계               666845000        424627950    
      총합계                  5303110629       7109308905

ROLLUP 을 공부하는데 조금만 도와주시면 감사 하겠습니다.
감사합니다.
이 게시물을
1  COMMENTS
  • Profile
    윤병진 • 2004.06.02 18:26





    SELECT * FROM
    (
        SELECT
              DECODE(GROUPING(CLCD),'1','SUB_TOTAL', CLCD) AS CLCD
              DECODE(GROUPING(CLNM),'1','SUB_TOTAL', CLNM) AS CLNM
              DECODE(GROUPING(YEAR),'1','SUB_TOTAL', YEAR) AS YEAR
              DECODE(GROUPING(MNTH),'1','SUB_TOTAL', MNTH)  AS MNTH
              SUM(CGMT) 청구금액,
              SUM(WJMT) 외주금액
        FROM
          (SELECT GA_CLCD CLCD,AD_CLNT CLNM, GA_YEAR YEAR,GA_MNTH MNTH,
               GA_CGMT CGMT,GA_WJMT WJMT
           FROM GAMSDB, SPLYDB
           WHERE
               GA_YEAR = '2004'
           AND GA_MNTH >= '01' AND GA_MNTH <= '04'
           AND GA_CLCD IN ('1010','1020')
           AND GA_CLCD = AD_KEYS(+)
          )

        GROUP BY ROLLUP(CLCD,CLNM,YEAR,MNTH)
    )

    WHERE
      
       (CLCD = 'SUB_TOTAL' AND CLNM != 'SUB_TOTAL' AND YEAR !='SUB_TOTAL' AND MONTH != 'SUB_TOTAL')
    OR (CLCD = 'SUB_TOTAL' AND CLNM = 'SUB_TOTAL' AND YEAR ='SUB_TOTAL' AND MONTH = 'SUB_TOTAL')


    ※ 위의 쿼리로는 네가지의 그룹이 생긴다
    ==================================================

    CLCD        CLNM        YEAR          MONTH    
    --------------------------------------------------
    SUB_TOTAL   A 회사       2004         01                   ->  첫번째...  요거하구...

    SUB_TOTAL   SUB_TOTAL    2004         01                   ->  두번째...

    SUB_TOTAL   SUB_TOTAL    SUB_TOTAL    01                   ->  세번째...

    SUB_TOTAL   SUB_TOTAL    SUB_TOTAL    SUB_TOTAL            ->  이것은 총계임...  요거만 출력..
←First Page
435 436 437 438 439 440 441 442 443 444
Last Page→
한델 로그인 하기