SQL枢轴中的总数

Question

我想让电子表格喜欢总计我拥有的SQL枢轴。下面的工作正常，以枢轴格式显示无小计的值。如果有一种方法可以同时获得行＆amp;总数的很棒。如果不是，那么至少在底部的总数就可以了。

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

您可以使用lollup和有条件的聚合：

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;

对于示例数据：

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;

输出：输出：

期间	完成	wip	not_started	总计
1	3	2	1	6
2	2	1	0	3
3	4	0	2	6
总计	9	3	3	15

db＆lt;＆gt; gt; fiddle 在这里