如何在给定SQL代码的rcount列上添加SUM聚合函数

你现有的SQL语句，是统计指定分类下每部电影的出租次数，输出字段为电影名称、分类名称、对应出租次数rcount。你要对rcount做SUM聚合，根据不同的使用场景，有以下两种常见修改方案：

方案1：在现有结果基础上新增汇总行，得到所有rcount的总和 #

可以用MySQL支持的WITH ROLLUP语法，直接在GROUP BY语句后追加即可，修改后的代码如下：

SELECT 
  IFNULL(`a.film_title`, '总计') AS `film_title`, 
  IFNULL(`a.category_name`, '') AS `category_name`, 
  COUNT(`a.film_title`) AS `rcount`
FROM 
  (
    SELECT 
      `f.title` AS `film_title`, 
      c.name AS `category_name` 
    FROM 
      film f 
      JOIN `film_category` b ON `b.film_id` = `f.film_id` 
      JOIN category c ON `c.category_id` = `b.category_id` 
      JOIN inventory i ON `i.film_id` = `f.film_id` 
      JOIN rental r ON `r.inventory_id` = `i.inventory_id` 
      AND c.name in (
        'Animation', 'Children', 'Classics', 
        'Comedy', 'Family', 'Music'
      )
  ) a 
GROUP BY 
  1, 2 WITH ROLLUP
ORDER BY 
  2, 1;

该方案的查询结果最后会多一行汇总，film_title显示为「总计」，rcount就是所有电影出租次数的总和。

方案2：给每一行都追加聚合统计值 #

如果你需要每一行都带上全局总和、或者对应分类的总和，可以用窗口函数实现，修改后的代码如下：

SELECT 
  `a.film_title`, 
  `a.category_name`, 
  COUNT(`a.film_title`) AS `rcount`,
  -- 全局所有rcount的总和
  SUM(COUNT(`a.film_title`)) OVER() AS global_total,
  -- 同分类下所有电影的rcount总和
  SUM(COUNT(`a.film_title`)) OVER(PARTITION BY `a.category_name`) AS category_total
FROM 
  (
    SELECT 
      `f.title` AS `film_title`, 
      c.name AS `category_name` 
    FROM 
      film f 
      JOIN `film_category` b ON `b.film_id` = `f.film_id` 
      JOIN category c ON `c.category_id` = `b.category_id` 
      JOIN inventory i ON `i.film_id` = `f.film_id` 
      JOIN rental r ON `r.inventory_id` = `i.inventory_id` 
      AND c.name in (
        'Animation', 'Children', 'Classics', 
        'Comedy', 'Family', 'Music'
      )
  ) a 
GROUP BY 
  1, 2 
ORDER BY 
  2, 1;

你可以根据实际需要保留对应聚合字段即可。

内容的提问来源于stack exchange，提问作者Israel Owusu Ansah