Totals in SQL Pivot

Question

I would like to get spreadsheet like to totals to this SQL Pivot I have. Below works fine to display the values in pivot format without subtotals. If there is a way to get both row & column total's that would be great. If not, at least total at the bottom would be fine.

SELECT * 
  FROM (SELECT period, status
          FROM tasks )
 PIVOT
 (
  COUNT(status)
    FOR status IN ('Completed' AS "Completed", 
                   'WIP' AS "WIP",
                   'Not Started' AS "Not Started")
 )
ORDER BY period

Answer 1

Rather than using PIVOT, you can use ROLLUP and conditional aggregation:

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;

Which, for the sample data:

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;

Outputs:

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

db<>fiddle here