mysql - 多个GROUP BY + COUNT在同一个表上显示0值

Question

我有这个带有 2 个分组依据的查询，并且我尝试显示 0 个值。

SELECT plan, locale, COUNT( * ) AS res
FROM  domain 
WHERE status LIKE  "active"
AND plan IS NOT NULL 
GROUP BY plan, locale

我知道我必须在同一张表上进行左连接，但我无法使其工作

SELECT d1.plan, d1.locale, IFNULL(COUNT(d2.id), 0) AS res
FROM domain AS d1
LEFT JOIN domain AS d2 ON d1.id = d2.id
WHERE d1.plan IS NOT NULL 
GROUP BY d1.plan, d1.locale

我做错了什么？

感谢大家的帮助。

Answer 1

不要使用 where 子句。使用条件聚合:

SELECT plan, locale,
       sum(case when status LIKE 'active' AND plan IS NOT NULL then 1 else 0 end) as res
FROM  domain 
GROUP BY plan, locale;

编辑:

SELECT plan, locale,
       sum(case when status LIKE 'active' then 1 else 0 end) as res
FROM  domain 
WHERE plan is not null
GROUP BY plan, locale;