SQLite中如何为现有统计查询结果添加总计列E？

给你两种简单的方法添加总计列'E'：

方法一：直接统计总行数（最简便高效） #

因为你的A、B、C、D四个列已经覆盖了所有Total的可能取值，它们的总和就是表中所有记录的数量，所以直接用COUNT(*)就能得到总计：

SELECT
  SUM(CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
  SUM(CASE WHEN Total BETWEEN 2.00 AND 6.99 THEN 1 ELSE 0 END) AS 'B',
  SUM(CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
  SUM(CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D',
  COUNT(*) AS 'E' -- 新增的总计列
FROM
  invoices;

方法二：计算A+B+C+D的和 #

如果想直观体现总计是四个分类的总和，可以先算出A-D的值，再求和：

SELECT
  A,
  B,
  C,
  D,
  A + B + C + D AS 'E'
FROM (
  -- 原查询作为子查询先算出A-D的值
  SELECT
    SUM(CASE WHEN Total < 2.00 THEN 1 ELSE 0 END) AS 'A',
    SUM(CASE WHEN Total BETWEEN 2.00 AND 6.99 THEN 1 ELSE 0 END) AS 'B',
    SUM(CASE WHEN Total BETWEEN 7.00 AND 15.00 THEN 1 ELSE 0 END) AS 'C',
    SUM(CASE WHEN Total > 15.00 THEN 1 ELSE 0 END) AS 'D'
  FROM invoices
) AS temp;

另外提个小细节：你原来的ORDER BY BillingCity没有实际作用，因为当前查询是无分组的聚合查询，只会返回一行结果，排序不会生效，可以去掉。

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