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

Question

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 by the answerer. I was just wondering if there any alternative solution without compromising performance?

Also, I don't understand why a query like:

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

will return empty result where only without the 'GROUP BY' part it shows expected result with 0 as count value? Is this can be a possible mysql bug?

I have also found a discussion on mysql bug portal, with similar issue, here: http://bugs.mysql.com/bug.php?id=26087

But this discussion isn't matured enough to get a suitable answer, I guess.

My Questions:

• Is this could be a possible mysql bug?
• Is is possible to have a better alternative solution than the given one(the link at the beginning)? If yes, how?

Answer 1

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

Answer 2

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.