통계 쿼리문

SELECT
sum(od_settle_case='신용카드') AS od_card 
, (SELECT SUM(od_cart_price+od_send_cost+od_send_cost2) FROM g5_shop_order WHERE od_status = '주문' and od_settle_case = '신용카드' AND od_time >= '2015-11-01 00:00:00' and od_time < '2015-11-31 23:59:59') AS od_card_price
, SUM(od_settle_case='무통장') AS od_bankbook
, (SELECT SUM(od_cart_price+od_send_cost+od_send_cost2) FROM g5_shop_order WHERE od_status = '주문' and od_settle_case = '무통장' AND od_time >= '2015-11-01 00:00:00' and od_time < '2015-11-31 23:59:59') AS od_card_price
, SUM(od_settle_case='계좌이체') AS od_transfer
, (SELECT SUM(od_cart_price+od_send_cost+od_send_cost2) FROM g5_shop_order WHERE od_status = '주문' and od_settle_case = '계좌이체' AND od_time >= '2015-11-01 00:00:00' and od_time < '2015-11-31 23:59:59') AS od_card_price
, ROUND(SUM(od_settle_case='신용카드') / COUNT(*) * 100, 2) AS od_card_per
, ROUND(SUM(od_settle_case='무통장') / COUNT(*) * 100, 2) AS od_bankbook_per
, ROUND(SUM(od_settle_case='계좌이체') / COUNT(*) * 100, 2) AS od_transfer_per
, ROUND((SUM(CASE WHEN od_settle_case = '신용카드' THEN od_receipt_price + od_send_cost + od_send_cost2 ELSE 0 END) / SUM(od_receipt_price + od_send_cost + od_send_cost2)) * 100, 2) AS od_card_price_per
, ROUND((SUM(CASE WHEN od_settle_case = '무통장' THEN od_receipt_price + od_send_cost + od_send_cost2 ELSE 0 END) / SUM(od_receipt_price + od_send_cost + od_send_cost2)) * 100, 2) AS od_cash_price_per  
, ROUND((SUM(CASE WHEN od_settle_case = '계좌이체' THEN od_receipt_price + od_send_cost + od_send_cost2 ELSE 0 END) / SUM(od_receipt_price + od_send_cost + od_send_cost2)) * 100, 2) AS od_transfer_price_per
FROM g5_shop_order 
WHERE od_status = '주문' AND od_time >= '2015-11-01 00:00:00' and od_time < '2015-11-31 23:59:59';


--------> 변경


SELECT
    SUM(CASE WHEN od_settle_case = '신용카드' THEN 1 ELSE 0 END) AS od_card
    , SUM(CASE WHEN od_settle_case = '무통장' THEN 1 ELSE 0 END) AS od_bankbook
    , SUM(CASE WHEN od_settle_case = '계좌이체' THEN 1 ELSE 0 END) AS od_transfer
    
    , SUM(CASE WHEN od_settle_case = '신용카드' THEN od_cart_price_total ELSE 0 END) AS od_card_price
    , SUM(CASE WHEN od_settle_case = '무통장' THEN od_cart_price_total ELSE 0 END) AS od_bankbook_price
    , SUM(CASE WHEN od_settle_case = '계좌이체' THEN od_cart_price_total ELSE 0 END) AS od_transfer_price
    
    , ROUND(SUM(CASE WHEN od_settle_case = '신용카드' THEN 1 ELSE 0 END) / COUNT(od_id) * 100, 2) AS od_card_per
    , ROUND(SUM(CASE WHEN od_settle_case = '무통장' THEN 1 ELSE 0 END) / COUNT(od_id) * 100, 2) AS od_bankbook_per
    , ROUND(SUM(CASE WHEN od_settle_case = '계좌이체' THEN 1 ELSE 0 END) / COUNT(od_id) * 100, 2) AS od_transfer_per
    
    , ROUND(SUM(CASE WHEN od_settle_case = '신용카드' THEN od_cart_price_total ELSE 0 END) / SUM(od_cart_price_total) * 100, 2) AS od_card_price_per
    , ROUND(SUM(CASE WHEN od_settle_case = '무통장' THEN od_cart_price_total ELSE 0 END) / SUM(od_cart_price_total) * 100, 2) AS od_bankbook_price_per
    , ROUND(SUM(CASE WHEN od_settle_case = '계좌이체' THEN od_cart_price_total ELSE 0 END) / SUM(od_cart_price_total) * 100, 2) AS od_transfer_price_per
FROM (
    SELECT
        od_id
        , od_settle_case
        , od_cart_price + od_send_cost + od_send_cost2 - (od_discount_price + od_cart_discount + od_cart_coupon + od_point_use) AS od_cart_price_total
    FROM g5_shop_order
    WHERE od_status IN ('주문', '입금', '준비', '상품', '완료', '취소', '환불요청', '환불완료')
      AND od_time >= '2023-01-01 00:00:00'
      AND od_time < '2023-12-31 23:59:59'
) AS subquery;