[Oracle] UNION 활용해 매출, 매입 SQL 작성

일계, 월계 매입매출

예시

이와 같이 나타나도록 작성할 것이다.

사용 테이블

a - 상품마스터

d - 매출

c0 - 매입전표 헤더

c1 - 매입전표 바디

쿼리

/*매출 일계*/
/*s_amt:매출총액, v_amt:부가세, qty:수량, type:거래형태*/
SELECT SUM(d.s_amt) - SUM(d.v_amt * d.qty)  AS "매출액",  
       SUM(d.v_amt * d.qty)                 AS "부가세",
       SUM(d.s_amt)                         AS "매출총액",
       SUM(d.s_amt) - SUM(d.w_amt)          AS "매출이익",
       SUM(DECODE(a.type, '0', d.s_amt, 0)) AS "직영매출", /*0이면 직영매출*/
       SUM(DECODE(a.type, '2', d.s_amt, 0)) AS "수수료매출", /*2이면 수수료매출*/
       SUM(DECODE(a.type, '1', d.s_amt, 0)) AS "임대매출" /*1이면 임대매출*/
  FROM a /*점상품마스터*/
     , d /*매출*/
 WHERE a.mall_code = d.mall_code /*매장*/
   AND a.sto_code  = d.sto_code /*점포*/
   AND a.sku_code  = d.sku_code /*상품*/
   AND a.mall_code = '1234'
   AND a.sto_code  = '1'
   AND d.date      = '20240827' /*날짜*/

 UNION ALL
/*매출 월계*/
SELECT SUM(d.s_amt) - SUM(d.v_amt * d.qty)  AS "매출액", 
       SUM(d.v_amt * d.qty)                 AS "부가세",
       SUM(d.s_amt)                         AS "매출총액",
       SUM(d.s_amt) - SUM(d.w_amt)          AS "매출이익",
       SUM(DECODE(a.type, '0', d.s_amt, 0)) AS "직영매출", /*0이면 직영매출*/
       SUM(DECODE(a.type, '2', d.s_amt, 0)) AS "수수료매출", /*2이면 수수료매출*/
       SUM(DECODE(a.type, '1', d.s_amt, 0)) AS "임대매출" /*1이면 임대매출*/
  FROM a /*점상품마스터*/
     , d /*매출*/
 WHERE a.mall_code             = d.mall_code
   AND a.sto_code              = d.sto_code
   AND a.sku_code              = d.sku_code
   AND a.mall_code             = '1234'
   AND a.sto_code              = '1'
   AND SUBSTRING(d.date, 1, 6) = '202408'

 UNION ALL
/*매입 일계*/
/*flag:매입구분(0:정상, 1:반품), wprc:원가단가, vat:부가세단가, p_wamt:원가*/
SELECT SUM(DECODE(c0.flag, '0', 1, '1', -1) * (c1.wprc * c1.qty)) AS "매입액",
       SUM(DECODE(c0.flag, '0', 1, '1', -1) * (c1.vat * c1.qty))  AS "부가세",
       SUM(DECODE(c0.flag, '0', 1, '1', -1) * c1.p_wamt)          AS "매입총액",
       NULL      /*이런식으로 NULL AS해줘도 되는데*/              AS "매출이익",
       NULL      /*이렇게 NULL 직영매출 이런식으로 해줘도 됨.*/      "직영매출",
       NULL                                                       AS "수수료매출",
       NULL                                                       AS "임대매출" 
  FROM c0 /*매입전표 헤더*/
     , c1 /*매입전표 바디*/
 WHERE c0.mall_code = c1.mall_code
   AND c0.date      = c1.date
   AND c0.sto_code  = c1.sto_code
   AND c0.slip_num  = c1.slip_num
   AND c1.mall_code = '1234'
   AND c1.sto_code  = '1'
   AND c1.date      = '20240827'

 UNION ALL
/*매입 월계*/
SELECT SUM(DECODE(c0.flag, '0', 1, '1', -1) * ROUND(c1.wprc * c1.qty)) AS "매입액",
       SUM(DECODE(c0.flag, '0', 1, '1', -1) * (c1.vat * c1.qty))       AS "부가세",
       SUM(DECODE(c0.flag, '0', 1, '1', -1) * c1.p_wamt)               AS "매입총액",
       NULL                                                            AS "매출이익", 
       NULL                                                            AS "직영매출",
       NULL                                                            AS "수수료매출",
       NULL                                                            AS "임대매출" 
  FROM c0 /*매입전표 헤더*/
     , c1 /*매입전표 바디*/
 WHERE c0.mall_code             = c1.mall_code
   AND c0.date                  = c1.date
   AND c0.sto_code              = c1.sto_code
   AND c0.slip_num              = c1.slip_num
   AND c1.mall_code             = '1234'
   AND c1.sto_code              = '1'
   AND SUBSTRING(c1.date, 1, 6) = '202408';

결과

이와 같이 나타났다.

일별/월별 매입 대비 이익률

위의 쿼리를 활용해 일별, 월별 매입 대비 이익률을 나타내보도록 하자.

쿼리

SELECT ROUND(((A.sales - B.purchase) / A.sales) * 100, 2) AS "일별 월별 매입 대비 이익율", /*소수점아래 두자리수까지*/
       A.sales - B.purchase                               AS "이익액"
  FROM (SELECT SUM(d.s_amt) AS sales /*총 매출*/
          FROM a /*점상품마스터*/
             , d /*매출*/
         WHERE a.mall_code = d.mall_code /*매장*/
           AND a.sto_code  = d.sto_code /*점포*/
           AND a.sku_code  = d.sku_code /*상품*/
           AND a.mall_code = '1234'
           AND a.sto_code  = '1'
           AND d.date      = '20240827' /*날짜*/) A,
       /*flag:매입구분(0:정상, 1:반품), p_wamt:원가*/
       (SELECT SUM(DECODE(c0.flag, '0', 1, '1', -1) * c1.p_wamt) AS purchase /*총 매입*/
          FROM c0 /*매입전표 헤더*/
             , c1 /*매입전표 바디*/
         WHERE c0.mall_code = c1.mall_code /*매장*/
           AND c0.date      = c1.date /*날짜*/
           AND c0.sto_code  = c1.sto_code /*점포*/
           AND c0.slip_num  = c1.slip_num /*전표번호*/
           AND c1.mall_code = '1234'
           AND c1.sto_code  = '1'
           AND c1.date      = '20240827' /*날짜*/) B

 UNION ALL

SELECT ROUND(((A.sales - B.purchase) / A.sales) * 100, 2) AS "일별 월별 매입 대비 이익율",/*소수점아래 두자리수까지*/
       A.sales - B.purchase                               AS "이익액"
  FROM (SELECT SUM(d.s_amt) AS sales /*총 매출*/
          FROM a /*점상품마스터*/
             , d /*매출*/
         WHERE a.mall_code             = d.mall_code /*매장*/
           AND a.sto_code              = d.sto_code /*점포*/
           AND a.sku_code              = d.sku_code /*상품*/
           AND a.mall_code             = '1234'
           AND a.sto_code              = '1'
           AND SUBSTRING(d.date, 1, 6) = '202408') A, /*년월*/
       /*flag:매입구분(0:정상, 1:반품), p_wamt:원가*/
       (SELECT SUM(DECODE(c0.flag, '0', 1, '1', -1) * c1.p_wamt) AS purchase /*총 매입*/
          FROM c0 /*매입전표 헤더*/
             , c1 /*매입전표 바디*/
         WHERE c0.mall_code             = c1.mall_code /*매장*/
           AND c0.date                  = c1.date /*날짜*/
           AND c0.sto_code              = c1.sto_code /*점포*/
           AND c0.slip_num              = c1.slip_num /*전표번호*/
           AND c1.mall_code             = '1234'
           AND c1.sto_code              = '1'
           AND SUBSTRING(c1.date, 1, 6) = '202408' /*년월*/) B

결과

위와 같이 나타나는 걸 확인할 수 있다. (위가 일별, 아래가 월별)