如何对SQL count分组查询得到的多个统计值进行求和？

可行方案有两种，按需选择即可： #

注意：你原SQL中SELECT里包含了f.filmid、filmtitel两个非聚合字段，但GROUP BY只写了FA.award_type，在开启严格SQL模式（如MySQL的ONLY_FULL_GROUP_BY）的环境下会执行报错，下面给出的优化写法都补全了必要的分组字段。

方案1：不需要保留各奖项类型的单独统计值，直接求总获奖数 #

你现有SQL加了GROUP BY FA.award_type才会按奖项类型拆分统计，去掉奖项类型的分组就能直接得到总和，写法最简单：

SELECT f.filmid, f.filmtitel, count(FA.award_type) AS total_award_count
FROM film F
INNER JOIN film_award_uitreiking FAU ON F.filmid = FAU.filmid
INNER JOIN Film_award FA ON FA.awardid = FAU.awardid
WHERE fau.filmid = 4317
GROUP BY f.filmid, f.filmtitel;

方案2：需要同时保留各奖项类型的单独统计值 + 总求和值 #

可以用窗口函数，不需要嵌套查询就能同时拿到拆分统计和总和：

SELECT 
  f.filmid, 
  f.filmtitel, 
  FA.award_type,
  count(FA.award_type) AS award_count_by_type,
  SUM(count(FA.award_type)) OVER (PARTITION BY f.filmid) AS total_award_count
FROM film F
INNER JOIN film_award_uitreiking FAU ON F.filmid = FAU.filmid
INNER JOIN Film_award FA ON FA.awardid = FAU.awardid
WHERE fau.filmid = 4317
GROUP BY f.filmid, f.filmtitel, FA.award_type
ORDER BY award_count_by_type DESC;

如果你的数据库不支持窗口函数，也可以用子查询嵌套实现：

SELECT 
  filmid,
  filmtitel,
  SUM(award_count) AS total_award_count
FROM (
  SELECT f.filmid, filmtitel, count(award_type) AS award_count
  FROM film F
  INNER JOIN film_award_uitreiking FAU ON F.filmid = FAU.filmid
  INNER JOIN Film_award FA ON FA.awardid = FAU.awardid
  WHERE fau.filmid = 4317
  GROUP BY FA.award_type, f.filmid, f.filmtitel
) t
GROUP BY filmid, filmtitel;

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