GROUP BY · HAVING 절

-- Oracle: 연습용 스키마에서 최초 한 번만 실행
CREATE TABLE BOOK_SALE (
    SALE_ID     NUMBER(4) PRIMARY KEY,
    BRANCH_ID   VARCHAR2(2 CHAR) NOT NULL,
    CHANNEL     VARCHAR2(10 CHAR) NOT NULL,
    AMOUNT      NUMBER(9),
    SALE_STATUS VARCHAR2(10 CHAR) NOT NULL,
    CONSTRAINT CK_BS_AMOUNT CHECK (AMOUNT >= 0),
    CONSTRAINT CK_BS_STATUS CHECK (SALE_STATUS IN ('PAID','PENDING'))
);

INSERT INTO BOOK_SALE VALUES (1001, 'B1', 'ONLINE', 12000, 'PAID');
INSERT INTO BOOK_SALE VALUES (1002, 'B1', 'STORE',  18000, 'PAID');
INSERT INTO BOOK_SALE VALUES (1003, 'B1', 'ONLINE', NULL,  'PENDING');
INSERT INTO BOOK_SALE VALUES (1004, 'B2', 'ONLINE', 20000, 'PAID');
INSERT INTO BOOK_SALE VALUES (1005, 'B2', 'STORE',  30000, 'PAID');
INSERT INTO BOOK_SALE VALUES (1006, 'B2', 'ONLINE', 10000, 'PAID');
INSERT INTO BOOK_SALE VALUES (1007, 'B3', 'STORE',  15000, 'PAID');
INSERT INTO BOOK_SALE VALUES (1008, 'B3', 'ONLINE', NULL,  'PENDING');
INSERT INTO BOOK_SALE VALUES (1009, 'B3', 'ONLINE', 25000, 'PAID');
COMMIT;

-- 지점별로 9행을 3개 그룹으로 묶는다.
SELECT BRANCH_ID,
       COUNT(*) AS RECORD_CNT,
       COUNT(AMOUNT) AS AMOUNT_CNT,
       SUM(AMOUNT) AS TOTAL_AMT,
       AVG(AMOUNT) AS AVG_AMT
  FROM BOOK_SALE
 GROUP BY BRANCH_ID
 ORDER BY BRANCH_ID;

-- B1의 결과: 3, 2, 2, 30000, 15000, 18000, 12000
SELECT COUNT(*) AS ALL_CNT,
       COUNT(AMOUNT) AS VALUE_CNT,
       COUNT(DISTINCT AMOUNT) AS DISTINCT_CNT,
       SUM(AMOUNT) AS TOTAL_AMT,
       AVG(AMOUNT) AS AVG_AMT,
       MAX(AMOUNT) AS MAX_AMT,
       MIN(AMOUNT) AS MIN_AMT
  FROM BOOK_SALE
 WHERE BRANCH_ID = 'B1';

-- NULL을 0으로 바꾼 평균은 다른 의미: 10000
SELECT AVG(NVL(AMOUNT, 0)) AS AVG_WITH_ZERO
  FROM BOOK_SALE
 WHERE BRANCH_ID = 'B1';

-- A. 전체 판매 기록을 지점별로 집계: B1, B2, B3
SELECT BRANCH_ID, COUNT(*) AS RECORD_CNT, SUM(AMOUNT) AS TOTAL_AMT
  FROM BOOK_SALE
 GROUP BY BRANCH_ID
 ORDER BY BRANCH_ID;

-- B. 2만 원 이상 판매 행만 집계: B2(2건/50000), B3(1건/25000)
SELECT BRANCH_ID, COUNT(*) AS RECORD_CNT, SUM(AMOUNT) AS TOTAL_AMT
  FROM BOOK_SALE
 WHERE AMOUNT >= 20000
 GROUP BY BRANCH_ID
 ORDER BY BRANCH_ID;

-- C. 먼저 지점별 합계를 구한 후 4만 원 이상 그룹만: B2, B3
SELECT BRANCH_ID, COUNT(*) AS RECORD_CNT, SUM(AMOUNT) AS TOTAL_AMT
  FROM BOOK_SALE
 GROUP BY BRANCH_ID
HAVING SUM(AMOUNT) >= 40000
 ORDER BY BRANCH_ID;

-- D. 결제 완료 행만 대상으로 3건 이상 지점: B2만
SELECT BRANCH_ID, COUNT(*) AS RECORD_CNT, SUM(AMOUNT) AS TOTAL_AMT
  FROM BOOK_SALE
 WHERE SALE_STATUS = 'PAID'
 GROUP BY BRANCH_ID
HAVING COUNT(*) >= 3
 ORDER BY BRANCH_ID;

-- GROUP BY가 없으면 전체 9행을 하나의 그룹으로 간주: 9 한 행
SELECT COUNT(*) AS ALL_SALES
  FROM BOOK_SALE
HAVING COUNT(*) >= 8;

-- 그룹 자체가 조건을 만족하지 않으면 결과 0행
SELECT COUNT(*) AS ALL_SALES
  FROM BOOK_SALE
HAVING COUNT(*) >= 10;

-- 구버전에서도 사용할 수 있는 형태: B2(60000), B3(40000)
SELECT BRANCH_ID, SUM(AMOUNT) AS TOTAL_AMT
  FROM BOOK_SALE
 GROUP BY BRANCH_ID
HAVING SUM(AMOUNT) >= 40000
 ORDER BY TOTAL_AMT DESC, BRANCH_ID;