How do I refactor this SQL query?

Question

Hi,

I have a table called users with a column called activated_at, and I want to count how many users have been activated by checking if the column is null or not. And then display them side by side like this:

+----------+-----------+---------------+-------+
| Malaysia | Activated | Not Activated | Total |
+----------+-----------+---------------+-------+
| Malaysia |      5487 |           303 |  5790 | 
+----------+-----------+---------------+-------+

So this is my SQL:

select "Malaysia",
    (select count(*) from users where activated_at is not null and locale='en' and  date_format(created_at,'%m')=date_format(now(),'%m')) as "Activated",
    (select count(*) from users where activated_at is null and locale='en' and  date_format(created_at,'%m')=date_format(now(),'%m')) as "Not Activated",
    count(*) as "Total"
    from users 
    where locale="en"
    and  date_format(created_at,'%m')=date_format(now(),'%m');

In my code, I have to specify all the where statements three times, which is obviously redundant. How can I refactor this?

Regards, MK.

Answer 1

Not sure if MySql supports the CASE construct but I usually deal with this type of issue by doing something like,

select "Malaysia",
    SUM(CASE WHEN activated_at is not null THEN 1 ELSE 0 END) as "Activated",
    SUM(CASE WHEN activated_at is null THEN 1 ELSE 0 END as "Not Activated",
    count(*) as "Total"
from users 
where locale="en" and  date_format(created_at,'%m')=date_format(now(),'%m');

Answer 2

SELECT 
    COUNT( CASE WHEN activated_at IS NOT NULL THEN 1 ELSE 0 END) as "Activated",
    COUNT( CASE WHEN activated_at IS NULL THEN 1 ELSE 0 END) as "Not Activated",
    COUNT(*) as "Total"
FROM users WHERE locale="en" AND date_trunc('month', now()) = date_trunc('month' ,created_at);

Answer 3

I think this would work .. untested though:

select "Malaysia",
    (select count(*) from users2 where activated_at is not null) as "Activated",
    (select count(*) from users2 where activated_at is null) as "Not Activated",
    count(*) as "Total"
    from (select * from users where locale='en' and  date_format(created_at,'%m')=date_format(now(),'%m')) users2

p/s: Glad to see another Malaysian here ;)

EDIT: that won't work .. sorry .. use the CASE WHEN as suggested by others .. i wish i could delete this answer ..