如何合并多张独立表的单列求和结果？求多表SUM总合计方法

首先注意你的WHERE条件存在逻辑优先级问题：AND的优先级高于OR，原条件会被解析为(passenger_count > 2 AND payment_type = 1) OR payment_type = 2，这可能不符合你的筛选意图。建议修改为用IN或者括号明确逻辑：

passenger_count > 2 AND payment_type IN (1, 2)

要得到四张表求和的总合计值，只需将UNION ALL拼接的各表求和结果作为子查询，在外层再做一次求和即可，以下是两种实现方式：

方法1：子查询嵌套求和 #

SELECT SUM(monthly_total) AS total_fare_amount
FROM (
  -- 一月数据求和
  SELECT SUM(fare_amount) AS monthly_total
  FROM `my-project-demo1-402016.yellowcabsnyc.jan2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
  
  UNION ALL
  
  -- 二月数据求和
  SELECT SUM(fare_amount) AS monthly_total
  FROM `my-project-demo1-402016.yellowcabsnyc.feb2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
  
  UNION ALL
  
  -- 三月数据求和（替换为你的第三张表）
  SELECT SUM(fare_amount) AS monthly_total
  FROM `my-project-demo1-402016.yellowcabsnyc.mar2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
  
  UNION ALL
  
  -- 四月数据求和（替换为你的第四张表）
  SELECT SUM(fare_amount) AS monthly_total
  FROM `my-project-demo1-402016.yellowcabsnyc.apr2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
) AS all_monthly_totals

方法2：WITH子句提升可读性 #

WITH monthly_sums AS (
  SELECT SUM(fare_amount) AS sum_fare
  FROM `my-project-demo1-402016.yellowcabsnyc.jan2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
  
  UNION ALL
  
  SELECT SUM(fare_amount) AS sum_fare
  FROM `my-project-demo1-402016.yellowcabsnyc.feb2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
  
  UNION ALL
  
  SELECT SUM(fare_amount) AS sum_fare
  FROM `my-project-demo1-402016.yellowcabsnyc.mar2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
  
  UNION ALL
  
  SELECT SUM(fare_amount) AS sum_fare
  FROM `my-project-demo1-402016.yellowcabsnyc.apr2020`
  WHERE passenger_count > 2 AND payment_type IN (1, 2)
)
SELECT SUM(sum_fare) AS total_fare_amount
FROM monthly_sums

原理说明 #

UNION ALL仅会将各表的求和结果拼接成多行数据，在外层对这些行的求和值再次执行SUM()，就能得到所有表的总合计值。

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