selecting COUNT(*) sometimes returns 0, and sometimes returns nothing

Question

I am trying to find the number of different things in my database, using SELECT COUNT(*). The problem is when there are zero --

For example,

SELECT COUNT(*) FROM `images` WHERE `approved` = '1'

If there are no results, I will still get a 0 back and can check $result['COUNT(*)'].

But with this query,

SELECT COUNT(*) , `first_name` , `last_name` , `email`
FROM `images`
WHERE `approved` = '0'
AND (
   `first_name` = ''
   AND `last_name` = ''
   AND `email` = ''
)
GROUP BY `first_name` , `last_name` , `email`

I just get an empty result set.
How can I just get a 0 if there are no results, with this query?

Answer 1

The reason for this is the first is a simple aggregation query, it returns a single ordinal value. The 2nd query is a group by query, which returns a row, a tuple of results. Since the queries have different return types they have different empty values.

Answer 2

try using IsNull(first_name,'') as if the columns have null they will not be ruturned.

Answer 3

You are performing two very distinctive operations using this method, and I would discourage this sort of query. The fully proper way to do this is to make it into two separate queries, and run them separately.

If you want, you could simply run the second query WITHOUT the count, and then get a count of the rows returned at the application level, and that would be acceptable, and THAT count would be equal to 0 where no rows are returned.

Answer 4

You can't "just get a zero" with that query, because you are asking it for a row. In the first query, you are asking for a one-element row, where the element is the count - and that always returns a number. But in the second, you are asking for a row, and when no such row exists, returning no rows is the right thing to do.

Answer 5

The second query gives you one result row for each unique set of (first_name, last_name, email) occurrences. If there are no records at all, there are no (first_name, last_name, email) combinations, no resulting row, and no COUNT(*) value...

Answer 6

Review Tony Andrew's answer to another question and see how he does a UNION with a conditional select statement to get the desired behavior.

Answer 7

It sounds to me like you want to do the grouping, but just count the ones that are approved.

SELECT COUNT(CASE WHEN approved = '0' THEN 1 END) AS Cnt,
      first_name , last_name , email
FROM images
GROUP BY first_name , last_name , email;