Totals in SQL Pivot-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

You can use common table expression such as

WITH t AS
(
  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
)
SELECT TO_CHAR(period) AS "period", "Completed", "WIP", "Not Started",
       "Completed"   "WIP"   "Not Started" AS "Total"
  FROM t  
UNION ALL
SELECT 'Total', SUM("Completed"), SUM("WIP"), SUM("Not Started"),
       SUM("Completed") SUM("WIP") SUM("Not Started") AS "Total" 
  FROM t