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.

## Advertisement

## 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.