데이터베이스 질문과 답변

GROUP BY로 묶은것과 다른기준으로 합계 구하는법 질문드립니다 0 4 838

by 닉네임길이제한은몇글 [SQL Query] [2019.08.29 22:35:24]

안녕하세요~ 먼저 봐주셔서 감사합니다. 
합계를 구하는데 애를 먹어서 질문을 올리게 되었습니다.

 

우선 GROUP BY 기준이 buy_dt(매수일자), currency(통화)인 매수금액 합계를 구하였습니다.

그런데,  추가로 매수일자 구분이 없는 '통화별 매수금액 합계'도 함께 보여주고 싶습니다.
하나의 쿼리에서 이런경우엔 어떻게 짜야할지 방법을 모르겠어서 질문드립니다.

 

아래에 1)예시쿼리와 2)현재 출력하고 있는 데이터와 3)원하는 데이터를 같이 올립니다.
(계좌번호와 상환여부 등은 편의상 같은값으로 통일하였습니다!)

 

감사합니다 !

 

<예시쿼리>

-- 계좌번호, 매수일자, 매수일련번호, 매수금액, 통화, 상환여부
WITH TMP (ACCOUNT, BUY_DT, BUY_NO, BUY_A, CURRENCY, REPAY_YN) AS
(

  SELECT 'A', '20190619', 1,  200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190621', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190718', 1,  800000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190719', 1,     500, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 1,  600000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 2,   99.99, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190725', 1,  100.28, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190801', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190809', 1, 1000000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1,   66000, 'JPY', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1, 1200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190823', 1,   13000, 'USD', '0' FROM DUAL
)
SELECT /* */
       buy_dt
     , currency
     , SUM(buy_a) AS SUM_A
  FROM TMP
 GROUP BY buy_dt, currency
 ORDER BY 1, 2

<현재쿼리 실행결과>

매수일자 통화 합계_매수금액
20190619 KRW 200000
20190621 KRW 100000
20190718 KRW 800000
20190719 USD 500
20190724 KRW 600000
20190724 USD 99.99
20190725 USD 100.28
20190801 KRW 100000
20190809 KRW 1000000
20190822 JPY 66000
20190822 KRW 1200000
20190823 USD 13000

 

<원하는 결과> -> 원래결과에 추가로 오직 통화별로만 구한 매수금액 합계를 같이 구하고싶습니다.

매수일자 통화 합계_매수금액
20190619 KRW 200000
20190621 KRW 100000
20190718 KRW 800000
20190719 USD 500
20190724 KRW 600000
20190724 USD 99.99
20190725 USD 100.28
20190801 KRW 100000
20190809 KRW 1000000
20190822 JPY 66000
20190822 KRW 1200000
20190823 USD 13000
　 JPY 66000
　 KRW 4000000
　 USD 13700.27

 

 

by 춘 [2019.08.30 04:21:54] 
-- 계좌번호, 매수일자, 매수일련번호, 매수금액, 통화, 상환여부
WITH TMP (ACCOUNT, BUY_DT, BUY_NO, BUY_A, CURRENCY, REPAY_YN) AS
(
 
  SELECT 'A', '20190619', 1,  200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190621', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190718', 1,  800000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190719', 1,     500, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 1,  600000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 2,   99.99, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190725', 1,  100.28, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190801', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190809', 1, 1000000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1,   66000, 'JPY', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1, 1200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190823', 1,   13000, 'USD', '0' FROM DUAL
)
SELECT
    *
FROM
    (
    SELECT
        buy_dt
        , currency
        , SUM(buy_a) AS SUM_A
    FROM TMP
    GROUP BY buy_dt, currency
    )
    UNION ALL
    (
    SELECT
        NULL AS buy_dt
        , CURRENCY
        , SUM(BUY_A) SUM_A
    FROM TMP
    GROUP BY CURRENCY
    )
ORDER BY 1, 2
;

 

by 춘 [2019.08.30 04:31:51] 
-- 계좌번호, 매수일자, 매수일련번호, 매수금액, 통화, 상환여부
WITH TMP (ACCOUNT, BUY_DT, BUY_NO, BUY_A, CURRENCY, REPAY_YN) AS
(
  SELECT 'A', '20190619', 1,  200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190621', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190718', 1,  800000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190719', 1,     500, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 1,  600000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 2,   99.99, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190725', 1,  100.28, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190801', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190809', 1, 1000000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1,   66000, 'JPY', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1, 1200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190823', 1,   13000, 'USD', '0' FROM DUAL
)
SELECT
    buy_dt
    , currency
    , SUM(buy_a) AS SUM_A
FROM TMP
GROUP BY currency, ROLLUP( buy_dt)
ORDER BY 1, 2
;

 

by 우리집아찌 [2019.08.30 09:52:25] 
-- 계좌번호, 매수일자, 매수일련번호, 매수금액, 통화, 상환여부
WITH TMP (ACCOUNT, BUY_DT, BUY_NO, BUY_A, CURRENCY, REPAY_YN) AS
(
 
  SELECT 'A', '20190619', 1,  200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190621', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190718', 1,  800000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190719', 1,     500, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 1,  600000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190724', 2,   99.99, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190725', 1,  100.28, 'USD', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190801', 1,  100000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190809', 1, 1000000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1,   66000, 'JPY', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190822', 1, 1200000, 'KRW', '0' FROM DUAL
  UNION ALL
  SELECT 'A', '20190823', 1,   13000, 'USD', '0' FROM DUAL
)
SELECT /* */
       buy_dt
     , currency
     , SUM(buy_a) AS SUM_A
     , GROUPING_ID(currency)
  FROM TMP
 GROUP BY ROLLUP (currency,(buy_dt))
 HAVING GROUPING_ID(currency) != 1
 ORDER BY 1, 2

 

by 닉네임길이제한은몇글 [2019.08.30 10:53:01]

두분다 감사드립니다

채택드리고싶은데 답변채택이 하나밖에 선택이 안되는것같습니다ㅠㅠ 

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