By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tasks (period, status) AS
SELECT 1, 'Completed' FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 1, 'WIP' FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 1, 'Not Started' FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 2, 'Completed' FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 2, 'WIP' FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 3, 'Completed' FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT 3, 'Not Started' FROM DUAL CONNECT BY LEVEL <= 2;
15 rows affected
SELECT CASE GROUPING_ID(period) WHEN 1 THEN 'TOTAL' ELSE TO_CHAR(period) END AS period,
COUNT(CASE status WHEN 'Completed' THEN 1 END) AS completed,
COUNT(CASE status WHEN 'WIP' THEN 1 END) AS wip,
COUNT(CASE status WHEN 'Not Started' THEN 1 END) AS not_started,
COUNT(*) AS total
FROM tasks
GROUP BY ROLLUP(period)
ORDER BY period;
| PERIOD | COMPLETED | WIP | NOT_STARTED | TOTAL |
|---|---|---|---|---|
| 1 | 3 | 2 | 1 | 6 |
| 2 | 2 | 1 | 0 | 3 |
| 3 | 4 | 0 | 2 | 6 |
| TOTAL | 9 | 3 | 3 | 15 |