PostgreSQL中如何计算两张不同表查询结果的总和？

我有两个查询语句，分别从两张不同的表中返回已完成的发行（issuances）和赎回（redemptions）的总数：

统计发行数（结果约18k）：

select
  count(*)
from
  issuances_extended
WHERE
  status = 'completed'

统计赎回数（结果约7k）：

select
  count(*)
from
  redemptions_extended
WHERE
  status = 'completed'

尝试用以下语句计算两者总和，但返回结果为2，统计错误：

with active_user as (
  select
    count(*) as issuance_count
  from
    issuances_extended
  where
    status = 'completed'
  UNION ALL
  select
    count(*) as redemption_count
  from
    redemptions_extended
  where
    status = 'completed'
  )
  select
    count(*)
  from
    active_user

错误原因 #

你的CTE通过UNION ALL返回了两行数据（一行是发行统计数，一行是赎回统计数），最后执行count(*)是统计行数，所以得到结果2，而非两个数值的总和。

正确做法 #

方法1：直接子查询相加

SELECT
  (SELECT count(*) FROM issuances_extended WHERE status = 'completed')
  +
  (SELECT count(*) FROM redemptions_extended WHERE status = 'completed') AS total;

方法2：CTE求和

调整CTE的字段名保持一致，最后对数值求和：

WITH stats AS (
  SELECT count(*) AS total_num FROM issuances_extended WHERE status = 'completed'
  UNION ALL
  SELECT count(*) AS total_num FROM redemptions_extended WHERE status = 'completed'
)
SELECT SUM(total_num) AS overall_total FROM stats;

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