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

我有这个查询与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

我究竟做错了什么？

感谢您的帮助。

最佳答案

不要使用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;

关于mysql - 多个GROUP BY + COUNT在同一表上显示0个值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21612915/