Getting Empty Results For 'COUNT'/'GROUP BY' MySQL Query

I am getting similar problem as the issue posted here: How can I get a non empty result set when using select, group by and count? However, the solution given is slower, mentioned

Solution 1:

This is not a MySQL bug.

An aggregation query will return one row per group that is encountered. Without a group by, there is one group -- the entire table. I admit this is a bit tricky, because there is still one group, even when there are no rows in the table.

With a group by, there is one row per group. If there are no rows in a group, then the group does not appear. In your case, the query:

SELECT `a`, `b`, COUNT(*) as `c`
FROM `mytable` 
WHERE `status` = 1

Will return one row, with two NULLs followed by 0.

The same query with:

GROUP BY `a`,`b`

will return no rows because there are no rows to form groups.

Solution 2:

In fact, it isn't a MySQL bug. But you can define a derived table first and then count its results. I think this behavior is because of the hierarchy of MySQL operations.

SELECT
`a`, `b`, COUNT(*) as `c`
FROM ( SELECT `a`, `b`
FROM `mytable` 
WHERE `status` = 1
GROUP BY `a`,`b`) t