WITH T AS (SELECT '20200731' AS DT, '가' AS B, 5 AS C FROM DUAL UNION ALL SELECT '20200730' AS DT, '나' AS B, 3 AS C FROM DUAL UNION ALL SELECT '20200730' AS DT, '가' AS B, 4 AS C FROM DUAL UNION ALL SELECT '20200628' AS DT, '나' AS B, 2 AS C FROM DUAL UNION ALL SELECT '20200501' AS DT, '다' AS B, 3 AS C FROM DUAL) SELECT DECODE ( GROUPING ( DT ), 1, '합계', DT ) AS DT ,DECODE ( GROUPING ( B ) ,1, ( SELECT COUNT ( * ) FROM ( SELECT B FROM T GROUP BY B) ) || '' ,B ) AS B ,SUM ( C ) AS C FROM T GROUP BY ROLLUP ( ( DT, B ) ) -- GROUPING, ROLL UP 참고 : http://www.gurubee.net/lecture/2679
WITH t AS ( SELECT '20200731' dt, '가' cd, 5 v FROM dual UNION ALL SELECT '20200730', '나', 3 FROM dual UNION ALL SELECT '20200629', '가', 4 FROM dual UNION ALL SELECT '20200628', '나', 2 FROM dual UNION ALL SELECT '20200501', '다', 3 FROM dual ) SELECT NVL(dt, '총합') dt , NVL(cd, COUNT(DISTINCT cd)) cd , SUM(v) v FROM t a GROUP BY ROLLUP((dt, cd)) ORDER BY a.dt DESC NULLS LAST ;