[Oracle/오라클] ROLLUP 소계 / 총계 표현하기

SELECT * FROM TB_POINT;

SELECT 
	NVL(CUSTOMER_CD,'총합') AS customer_cd,
	TO_CHAR(SUM(POINT),'999,999') AS 포인트합
FROM TB_POINT
	GROUP BY ROLLUP(CUSTOMER_CD)
	ORDER BY customer_cd;

SELECT

      NVL(CUSTOMER_CD, '소계') 고객번호,

      TO_CHAR(SUM(POINT), '999,999') 포인트합,

      COUNT(*) 인원

FROM

      TB_POINT GROUP BY

      ROLLUP(CUSTOMER_CD);

 

SELECT NVL(SALES_DT, '총합') AS SALES_DT,
	CASE 
    	WHEN SALES_DT IS NOT NULL AND PRODUCT_NM IS NULL THEN '<소계>'
		WHEN PRODUCT_NM IS NULL THEN '---------------'
        ELSE PRODUCT_NM
	END AS PRODUCT_NM,
	SUM(PRICE * SALES_COUNT) AS 매출금액
FROM TB_SALES
	GROUP BY ROLLUP(SALES_DT, PRODUCT_NM)
	ORDER BY SALES_DT;

SELECT NVL(SALES_DT, '총합') AS SALES_DT, 
        CASE 
            WHEN SALES_DT IS NOT NULL AND PRODUCT_NM IS NULL THEN '<소계>' 
            WHEN PRODUCT_NM IS NULL THEN '---------------' 
        ELSE PRODUCT_NM 
        END AS PRODUCT_NM, 
        SUM(PRICE * SALES_COUNT) AS 매출금액 
FROM TB_SALES GROUP 
BY ROLLUP(SALES_DT, PRODUCT_NM) 
ORDER BY SALES_DT;