MySQL : Avoid count with subquery

In SQL we often used subquery or joins to obtain a number of element (count) and especially when there are several numbers to get in a single query.

Fortunately, in some cases we can use a "trick" on function count() by passing conditions.

Syntax
count(condition) as Number

You could use a condition on fiel return by a query

count(IF(field='CONDITION',1,NULL)

Return 1 to add only if condition is true

example :
A query that return device use by member group by date from last 20 days

select

day(member.member_at) as Dday,

COUNT(IF(md.name = 'Android', 1, NULL)) as Android,

COUNT(IF(md.name = 'Ios', 1, NULL)) as Ios,

COUNT(IF(md.name is null, 1, NULL)) as NoPush

from member

left join member_device md on nhd.fk_node_human_id=nh.node_human_id

where

member.created_at> date_sub(now(), interval 20 day)

group by Dday;

Result

Dday Android Ios NoPush

23 0 4 0

24 4 9 3

25 12 13 5