Count data as zero if it is null when where clause is used
Your where condition on the outer joined table turns the outer join into an inner join (because the "non-existing rows will have a NULL
value and the comparison of NULL
with something else yields "undefined" and thus will remove that row from the result)
You need to move that condition into the join condition:
SELECT opp.name as name, count(log.stage_id) as stage_countFROM crm_lead as opp LEFT JOIN crm_lead_stage_log as log ON opp.id = log.opportunity_id AND log.create_date > '2014-01-28 08:49:03'GROUP BY name;
If you want return zero when the result is null, you can use the comand COALESCE.
SELECT opp.name AS name,COALESCE(COUNT(log.stage_id)),0) AS stage_countFROM crm_lead AS opp LEFT OUTER JOIN crm_lead_stage_log AS log ON (opp.id = log.opportunity_id)GROUP BY name
It return "0" when count get a null value.