Oracle PIVOT查询添加Total列时避免额外GROUP BY的方法

你可以直接将PIVOT生成的各用户类型计数列相加，得到每行的总计，不需要额外使用聚合函数或GROUP BY子句。因为PIVOT操作已经按status完成了分组聚合，每行的各个用户类型列都是对应状态下的统计值，直接求和即可。

修改后的SQL如下：

SELECT 
    p.*,
    "1" + "2" + "3" + "5" AS total
FROM
(
  SELECT status, user_type
  FROM
  (
    SELECT user_type, status 
    FROM transactions 
    WHERE status != 1  
      AND Update_Date BETWEEN TO_DATE('2022-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS') 
                          AND TO_DATE('2022-11-13 23:59:59', 'yyyy-mm-dd HH24:MI:SS') 
  )
)
PIVOT
(
  COUNT(*)
  FOR user_type IN (1,2,3,5)
) p
ORDER BY status asc;

如果想让列名更直观，可以给PIVOT的列指定别名后再求和：

SELECT 
    p.*,
    user_type_1 + user_type_2 + user_type_3 + user_type_5 AS total
FROM
(
  SELECT status, user_type
  FROM
  (
    SELECT user_type, status 
    FROM transactions 
    WHERE status != 1  
      AND Update_Date BETWEEN TO_DATE('2022-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS') 
                          AND TO_DATE('2022-11-13 23:59:59', 'yyyy-mm-dd HH24:MI:SS') 
  )
)
PIVOT
(
  COUNT(*)
  FOR user_type IN (
    1 AS user_type_1,
    2 AS user_type_2,
    3 AS user_type_3,
    5 AS user_type_5
  )
) p
ORDER BY status asc;

说明 #

PIVOT操作本质上已经按照status进行了分组，将每个user_type的计数转换为列。此时每行数据对应一个唯一的status，直接将该行的各计数列相加就能得到该状态下的总交易数，完全不需要再使用COUNT()聚合函数，自然不会触发"not a single-group group function"错误。

内容的提问来源于stack exchange，提问作者user20622377