Summing the total of 4 alias

I have the following query:

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'

Which outputs:

s1_count    s2_count    s3_count    s4_count    
  17           13           6           3

I want to have another column called “total” which has the sum of all 4 aliases.

Answer

Since you can’t use column aliases in the SELECT clause, there’s a couple of other ways you can do this. Either add another conditional aggregation to your query:

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,
SUM(case when s1 = 'fel' or s2 = 'fel' or s3 = 'fel' or s4 = 'fel' then 1 else 0 end) as total
FROM `arrest`
WHERE date BETWEEN '2018-12-01' AND '2019-03-01'

Note that this assumes that only one of s1, s2, s3 or s4 will be equal to fel for any given row.

The more robust method is to use your original query as a subquery:

SELECT *, s1_count+s2_count+s3_count+s4_count AS total
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') a

Note that since MySQL treats a boolean expression in a numeric context as either 1 (true) or 0 (false) you can simplify these queries as follows:

SELECT
SUM(s1 = 'fel') as s1_count,
SUM(s2 = 'fel') as s2_count,
SUM(s3 = 'fel') as s3_count,
SUM(s4 = 'fel') as s4_count,
SUM((s1 = 'fel') + (s2 = 'fel') + (s3 = 'fel') + (s4 = 'fel')) as total
FROM `arrest`
WHERE date BETWEEN '2018-12-01' AND '2019-03-01'

SELECT *, s1_count+s2_count+s3_count+s4_count AS total
FROM (SELECT
      SUM(s1 = 'fel') as s1_count,
      SUM(s2 = 'fel') as s2_count,
      SUM(s3 = 'fel') as s3_count,
      SUM(s4 = 'fel') as s4_count
      FROM `arrest`
      WHERE date BETWEEN '2018-12-01' AND '2019-03-01') a

Doing this removes the issue the first query had of requiring that only one of s1, s2, s3 or s4 will be equal to fel for any given row.