UNION 한 결과값의 ROW간 덧셈 가능한가요?? 0 3 1,348

by 코린이중에코린이 [SQL Query] SQL ROW간 덧셈 [2021.01.14 20:30:32]


1.PNG (16,115Bytes)
2.PNG (1,448Bytes)
3.PNG (7,481Bytes)

오라클 11g 환경에서

프로시져를 사용하지않고 엑셀없이 SQL 구문만으로 딱 나오게 해보라고 하여
이리저리해보고있는데 어렵네요. 

 

아래 구문의 목적은

2020년 01월 ~12월 사이의 MMS 발송건과 SMS발송건을 체크하여 매월 통신비가 얼마 나갔는지 체크하기 위함입니다.
현재 제가 못하고 있는 부분은 매월 MMS 비용과 SMS비용의 합을 3열에 표기하고 싶은데 못하고있습니다.

현재 맨아래 구문을 돌릴 경우

       MMS             SMS

1     156217.05       1681.9

2     73233.6          2698.3

3     44548.35        6824.4

4     74801.1          4513.3

.

.

12  17838.15          5856.4

로 나옵니다.

 


제가 원하는 결과 값은

       MMS             SMS         비용

1     156217.05       1681.9     157898.95

2     73233.6          2698.3     75.931.9

3     44548.35        6824.4     51,372.75

4     74801.1          4513.3     79.314.4

.

.

12  17838.15          5856.4     23694.55

로 나오게 만들고싶습니다.
되게 간단한거일거같은데 키워드를 떠올리지 못하는건지
연관되는 문장들을 구글링해도 도저히 못찾겠어서 문의드리고자 합니다.
긴 글 읽어주셔서 감사합니다.

 

 

아래는 제가 짠 코드입니다.


WITH SMS_PRICE(MMSS, SMSS) AS(

SELECT

(SELECT COUNT(msg_type) from MSG_RESULT_202001 WHERE result = 100 AND msg_type = 3) * 31.35 AS MMS,

(SELECT COUNT(msg_type) from MSG_RESULT_202001 WHERE result = 100 AND msg_type = 1) * 12.1 AS SMS

FROM dual

UNION ALL

SELECT

(SELECT COUNT(msg_type) from MSG_RESULT_202002 WHERE result = 100 AND msg_type = 3) * 31.35 AS MMS,

(SELECT COUNT(msg_type) from MSG_RESULT_202002 WHERE result = 100 AND msg_type = 1) * 12.1 AS SMS

FROM dual

UNION ALL

.

. -- 3월부터 12월까지 테이블명만 바꿔서 계속 진행

.

SELECT MMSS, SMSS FROM SMS_PRICE;

by 마농 [2021.01.15 08:28:23]
-- 그대로 고쳐보면 --
SELECT mmss
     , smss
     , mmss + smss tot    -- 그냥 더하시면 됩니다.
  FROM sms_price
;
-- 쿼리를 간결하게 바꿔보면 --
WITH tmp AS
(
SELECT 1 mm, result, msg_type FROM msg_result_202001
UNION ALL SELECT  2, result, msg_type FROM msg_result_202002
UNION ALL SELECT  3, result, msg_type FROM msg_result_202003
UNION ALL SELECT  4, result, msg_type FROM msg_result_202004
UNION ALL SELECT  5, result, msg_type FROM msg_result_202005
UNION ALL SELECT  6, result, msg_type FROM msg_result_202006
UNION ALL SELECT  7, result, msg_type FROM msg_result_202007
UNION ALL SELECT  8, result, msg_type FROM msg_result_202008
UNION ALL SELECT  9, result, msg_type FROM msg_result_202009
UNION ALL SELECT 10, result, msg_type FROM msg_result_202010
UNION ALL SELECT 11, result, msg_type FROM msg_result_202011
UNION ALL SELECT 12, result, msg_type FROM msg_result_202012
)
SELECT mm
     , COUNT(DECODE(msg_type, 3, 1)) * 31.35 mmss
     , COUNT(DECODE(msg_type, 1, 1)) * 12.10 smss
     , COUNT(DECODE(msg_type, 3, 1)) * 31.35 
     + COUNT(DECODE(msg_type, 1, 1)) * 12.10 tot
  FROM tmp
 WHERE result = 100
   AND msg_type IN (3, 1)
 GROUP BY mm
 ORDER BY mm
;

 


by 코린이중에코린이 [2021.01.19 14:03:47]

감사합니다. 생각이 매우 짧았군요.. 
DECODE 는 구글링을 통해 배울때 무조건 2가지 경우에 대해 써야하는줄알았는데 이렇게 쓰는방법도 있군요
배워갑니다.

혹시 프로시져를 사용하지않고 SQL 구문만 사용하면서도
변수를 사용이 가능할까요?? 
WITH tmp를 만들기전에

varchar 변수명 = "msg_result_2020"으로 
변수명에 msg_result_2020을 할당한다음
UNION ALL SELECT 2, result, msg_type FROM 변수명 || 01
UNION ALL SELECT 2, result, msg_type FROM 변수명 || 02

UNION ALL SELECT 2, result, msg_type FROM 변수명 || 03

UNION ALL SELECT 2, result, msg_type FROM 변수명 || 04
와 같은 방식으로 활용이 가능한지 궁금합니다.


by 마농 [2021.01.19 16:13:31]

정적 쿼리에서는 테이블명 컬럼명 등을 변수로 처리할 수는 없습니다.
그러려면 동적쿼리를 사용하시면 됩니다.
정적쿼리 만으로 처리하려면. 모든 연도를 다 union 한 후에 사용하세요.
매번 WITH 구문 길게 쓰지 마시고 VIEW 를 미리 만들어 두고 사용하세요.

WITH tmp AS
(
SELECT '2019' yyyy, 1 mm, result, msg_type FROM msg_result_201901
UNION ALL SELECT '2019',  2, result, msg_type FROM msg_result_201902
UNION ALL SELECT '2019',  3, result, msg_type FROM msg_result_201903
UNION ALL SELECT '2019',  4, result, msg_type FROM msg_result_201904
UNION ALL SELECT '2019',  5, result, msg_type FROM msg_result_201905
UNION ALL SELECT '2019',  6, result, msg_type FROM msg_result_201906
UNION ALL SELECT '2019',  7, result, msg_type FROM msg_result_201907
UNION ALL SELECT '2019',  8, result, msg_type FROM msg_result_201908
UNION ALL SELECT '2019',  9, result, msg_type FROM msg_result_201909
UNION ALL SELECT '2019', 10, result, msg_type FROM msg_result_201910
UNION ALL SELECT '2019', 11, result, msg_type FROM msg_result_201911
UNION ALL SELECT '2019', 12, result, msg_type FROM msg_result_201912
UNION ALL SELECT '2020',  1, result, msg_type FROM msg_result_202001
UNION ALL SELECT '2020',  2, result, msg_type FROM msg_result_202002
UNION ALL SELECT '2020',  3, result, msg_type FROM msg_result_202003
UNION ALL SELECT '2020',  4, result, msg_type FROM msg_result_202004
UNION ALL SELECT '2020',  5, result, msg_type FROM msg_result_202005
UNION ALL SELECT '2020',  6, result, msg_type FROM msg_result_202006
UNION ALL SELECT '2020',  7, result, msg_type FROM msg_result_202007
UNION ALL SELECT '2020',  8, result, msg_type FROM msg_result_202008
UNION ALL SELECT '2020',  9, result, msg_type FROM msg_result_202009
UNION ALL SELECT '2020', 10, result, msg_type FROM msg_result_202010
UNION ALL SELECT '2020', 11, result, msg_type FROM msg_result_202011
UNION ALL SELECT '2020', 12, result, msg_type FROM msg_result_202012
)
SELECT yyyy
     , mm
     , COUNT(DECODE(msg_type, 3, 1)) * 31.35 mmss
     , COUNT(DECODE(msg_type, 1, 1)) * 12.10 smss
     , COUNT(DECODE(msg_type, 3, 1)) * 31.35 
     + COUNT(DECODE(msg_type, 1, 1)) * 12.10 tot
  FROM tmp
 WHERE result = 100
   AND msg_type IN (3, 1)
   AND yyyy = '2020'         -- 연도 조건
 GROUP BY yyyy, mm
 ORDER BY yyyy, mm
;

 

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