안뿌꾸
80
2019-07-12 15:54:50
4
98

GROUP BY ROLLUP , DECODE 질문



SELECT

	DECODE(GROUPING(cust_no),1,'계좌합계',cust_no) cust_no
     , DECODE(GROUPING(acco_no),1,'?건',acco_no) acco_no
     , sum(bal_loan) bal_loan
     , sum(am_loan_int) am_loan_int
     , sum(am_loan_i_dly) am_loan_i_dly
  FROM (SELECT lc.cust_no cust_no
						 , ms.acco_no acco_no
						 , ms.bal_loan bal_loan
						 , lc.am_loan_int am_loan_int
						 , lc.am_loan_i_dly am_loan_i_dly
					 FROM a_savems ms, l_loancalc lc
					WHERE lc.cust_no IN ((SELECT cust_no
					       		             FROM (SELECT cust_no
					       		                        , id_loan
					       		                        , count(*)
					       		                     FROM a_savems
					       		                    WHERE id_loan	NOT IN ('GJ','##')
					       		                      AND basic_date = '20190331'
					       		                    GROUP BY cust_no, id_loan
					       		                   )
					                       GROUP BY cust_no
					                       HAVING count(*) > 2
					                       )
					                      )
					  AND ms.aclg_no = lc.aclg_no
					  AND ms.basic_date = lc.proc_date
					  AND ms.basic_date = '20190331'
					ORDER BY ms.cust_no
 )
GROUP BY ROLLUP (cust_no, acco_no)

;

//결과 값

고객번호계좌번호잔액이자연체료
1000063618128-01-13-0263881708377234141113058927952434
1000063618128-01-44-000451829673000
1000063618128-01-44-0004519458880000
1000063618128-01-44-00236901397500
1000063618128-01-76-049123330000000045610679647608
1000063618128-90-44-000012412264000
1000063618?건1013399379597219854928000042
1000070223128-01-44-0026981173565300
1000070223128-01-44-002724616929200
1000070223128-01-44-0035700187617100
1000070223128-01-73-00000351000000002935109550
1000070223128-02-13-0165986267998522513613262493973075951
1000070223128-02-76-00953809999840501609955663163454
1000070223?건378375039132647928673973239405
계좌합계?건479714977038620127214901239447


정답 :

고객번호계좌번호잔액이자연체료
1000063618128-01-13-0263881708377234141113058927952434
1000063618128-01-44-000451829673000
1000063618128-01-44-0004519458880000
1000063618128-01-44-00236901397500
1000063618128-01-73-00000193164320026541848480
1000063618128-01-76-049123330000000045610679647608
1000063618128-90-44-000012412264000
계좌합계        7건13298313811251404702928000042
1000070223128-01-44-0026981173565300
1000070223128-01-44-002724616929200
1000070223128-01-44-0035700187617100
1000070223128-01-73-00000351000000002935109550
1000070223128-02-13-0165986267998522513613262493973075951
1000070223128-02-76-00953809999840501609955663163454
계좌합계        6건378375039132647928673973239405
총합계       13건511358177245161975694901239447


정답처럼 나오게 하고 싶은데 생각보다 잘안되네요 건수는 count 하는것 같은데 총합계도 따로


 어찌 해야하나요?





0
0
  • 답변 4

  • saya
    64
    2019-07-12 16:54:07

    WITH t AS (

    SELECT '123' id , '001' aCcount , 100 amt1 , 20 amt2, 30 amt3 FROM dual UNION ALL

    SELECT '123' id , '002' aCcount , 200 amt1 , 30 amt2, 30 amt3 FROM dual UNION ALL

    SELECT '123' id , '003' aCcount , 100 amt1 , 20 amt2, 30 amt3 FROM dual UNION ALL

    SELECT '456' id , '004' aCcount , 100 amt1 , 20 amt2, 30 amt3 FROM dual UNION ALL

    SELECT '456' id , '005' aCcount , 50 amt1 , 10 amt2, 5 amt3 FROM dual UNION ALL

    SELECT '789' id , '006' aCcount , 70 amt1 , 15 amt2, 40 amt3 FROM dual 

    )

    SELECT CASE WHEN GROUPING(ID)= 0 AND GROUPING(ACCOUNT) = 1 THEN 'TOT' 

                WHEN GROUPING(ID)= 1 AND GROUPING(ACCOUNT) = 1 THEN 'TOTAL'

                ELSE ID END ID

    ,      CASE  GROUPING(ACCOUNT) WHEN 1 THEN COUNT(ACCOUNT)||''  ELSE ACCOUNT END ACCOUNT        

    ,      SUM(Amt1) amt1

    ,      SUM(Amt2) amt2

    ,      SUM(amt3) amt3

     FROM t

    GROUP BY ROLLUP(id, ACcOUNT);


    case when  으로 처리하는건 어떨까요~

    0
  • 안뿌꾸
    80
    2019-07-12 18:13:33


    SELECT
           CASE WHEN GROUPING(cust_no) = 1 AND GROUPING(acco_no) = 1 THEN '총합계'
              WHEN GROUPING(cust_no) = 0 AND GROUPING(acco_no) =1 THEN '계좌합계'
              ELSE 'cust_no' END cust_no


         , CASE GROUPING(acco_no) WHEN 1 THEN COUNT(acco_no)||'' ELSE acco_no END acco_no


         , sum(bal_loan) bal_loan
         , sum(am_loan_int) am_loan_int
         , sum(am_loan_i_dly) am_loan_i_dly


      FROM (SELECT lc.cust_no cust_no
           , ms.acco_no acco_no
           , ms.bal_loan bal_loan
           , lc.am_loan_int am_loan_int
           , lc.am_loan_i_dly am_loan_i_dly
          FROM a_savems ms, l_loancalc lc
         WHERE lc.cust_no IN ((SELECT cust_no
                               FROM (SELECT cust_no
                                          , id_loan
                                          , count(*)
                                       FROM a_savems
                                      WHERE id_loan NOT IN ('GJ','##')
                                        AND basic_date = '20190331'
                                      GROUP BY cust_no, id_loan
                                     )
                                GROUP BY cust_no
                                HAVING count(*) > 2
                                )
                               )
           AND ms.aclg_no = lc.aclg_no
           AND ms.basic_date = lc.proc_date
           AND ms.basic_date = '20190331'
         ORDER BY ms.cust_no
     )
    GROUP BY ROLLUP (cust_no, acco_no);

    cust_no 에서 오류나서 'cust_no' 로 정의해주었는데 고객번호가 전보 cust_no로 출력이 되네요

    이거는 어찌 처리해야하나요??


    0
  • 안뿌꾸
    80
    2019-07-12 18:14:58

    cust_no 128-01-13-0263881 708377234 141113058 927952434
    cust_no 128-01-44-0004518 296730 0 0
    cust_no 128-01-44-0004519 4588800 0 0
    cust_no 128-01-44-0023690 13975 0 0
    cust_no 128-01-76-0491233 300000000 456106796 47608
    cust_no 128-90-44-0000124 122640 0 0
    계좌합계 6 1013399379 597219854 928000042
    cust_no 128-01-44-0026981 1735653 0 0
    cust_no 128-01-44-0027246 169292 0 0
    cust_no 128-01-44-0035700 1876171 0 0
    cust_no 128-01-73-0000035 100000000 293510955 0
    cust_no 128-02-13-0165986 2679985225 1361326249 3973075951
    cust_no 128-02-76-0095380 999984050 1609955663 163454
    계좌합계 6 3783750391 3264792867 3973239405
    총합계 12 4797149770 3862012721 4901239447


    결과값이 이래 나옵니당

    0
  • saya
    64
    2019-07-12 22:36:40
    cust_no가 number 타입이면 cust_no ||'' 로 타입을 맞춰 주세요. 제가 올려드린 내용에도 count(aCcount) ||'' 로 타입을 맞춰서 출력하고 있습니다.
    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.