Does COUNT(*) always return a result?

mysql sql aggregate
Click Upvote picture Click Upvote · Mar 31, 2010 · Viewed 14.3k times · Source

If I run a query such as:

SELECT COUNT(*) as num FROM table WHERE x = 'y'

Will it always return a result, even when the query doesn't match any record? Or do I need to validate and make sure a row is returned as the result?

Answer

gbn picture gbn · Mar 31, 2010

Yes, because it's an aggregate and returns zero. Unless you add GROUP BY in which case no result because there is no group...

MAX/SUM etc would return NULL unless you add GROUP BY then no rows. Only COUNT returns a number for no results

Edit, a bit late: SUM would return NULL like MAX

Edit, May 2013: this applies to all main RDBMS. I guess as per the ANSI standard

Related questions

How to use GROUP BY to concatenate strings in MySQL?

Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C

Read More
SELECT list is not in GROUP BY clause and contains nonaggregated column

Receiving the following error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.country.Code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_…

Read More
Can I use non-aggregate columns with group by?

You cannot (should not) put non-aggregates in the SELECT line of a GROUP BY query. I would however like access the one of the non-aggregates associated with the max. In plain english, I want a table with the oldest id …

Read More