mysql - 总计 4 个别名

Question

我有以下查询:

SELECT
    SUM(case when s1 = 'fel' then 1 else 0 end) as s1_count,
    SUM(case when s2 = 'fel' then 1 else 0 end) as s2_count,
    SUM(case when s3 = 'fel' then 1 else 0 end) as s3_count,
    SUM(case when s4 = 'fel' then 1 else 0 end) as s4_count
FROM `arrest`
WHERE date BETWEEN '2018-12-01' AND '2019-03-01'

哪些输出:

s1_count    s2_count    s3_count    s4_count    
  17           13           6           3

我想要另一个名为“总计”的列，其中包含所有 4 个别名的总和。

Answer 1

你可以试试:

SELECT s1_count, s2_count, s3_count, s4_count, 
       SUM(s1_count + s2_count + s3_count + s4_count) as total_count
FROM 
(
    SELECT
        SUM(case when s1 = 'fel' then 1 else 0 end) as s1_count,
        SUM(case when s2 = 'fel' then 1 else 0 end) as s2_count,
        SUM(case when s3 = 'fel' then 1 else 0 end) as s3_count,
        SUM(case when s4 = 'fel' then 1 else 0 end) as s4_count
    FROM `arrest`
    WHERE date BETWEEN '2018-12-01' AND '2019-03-01'
) q