Does COUNT(*) always return a result?

Question

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?

Coan

@col yea :) ' , ' – Coan

@col by not trying and just asing it here, I guess he wants viewers to "Click Upvote" to his questions. – Colporteur

@Colporteur i'd have to actually create a test table and construct a query that would return 0 to test it, plus there could be differences between mysql versions that i don't know. – Coan

Answer 1

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

Heirship

What about functions like SUM(), would they return 0 or NULL? – Coan

@Click Upvote: sorry, I didn't answer. SUM gives NULL like MAX. Only COUNT returns 0 – Heirship

What is the type of COUNT(*)? Also see Numeric Types in the MySQL manual. – Universality

Answer 2

Yes, the return value of the "count" function itself is always a non-null integer, without exception.

Having said that, you may be more interested in the return value of your query, which is actually a result set. If that's the case, then you simply have to consider whether your query includes a "group by" clause.

A non-grouped query with aggregate functions like

select count(*), sum(*), max(*), min(*) from table

produces a result set with a single record containing the return value(s) of the aggregate function(s). In this case, your result set will always have exactly one record, with columns containing the return values of whatever aggregate functions you included. Within this lone record, the return value of count will always be a non-null integer, while the return values of the other aggregate functions such as min, max, and sum may be null. In the above example, your returned record may be "zero, null, null, null", but will never be "null,null,null,null", since count never returns null. So if you're calling only "count", then that one column in that one record returned will always contain the non-null return value of count; hence you can rest assured that the scalar return value of your count query will always be a non-null integer.

On the other hand, a grouped query with aggregate functions like

select count(*), sum(*), max(*), min(*) from table group by column

produces a result set with zero or more records where each record contains the return value of the aggregate function(s) for each group identified. In this case, if there are no groups identified, there are no groups for the aggregate functions to run on, the functions are never run, and the "query return value" will be an empty set with no records at all.

Test it out on a table with no records to see the results:

create table #t (c int);
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t; --returns one record {0, null, null, null}
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t group by c; --returns no records
drop table #t;

So whether you're calling count, min, max, or sum, it is important to understand not only the return values of individual aggregate functions, but also the "return value" of the query itself, and what that result set contains.

Fetation

Thanks! Just wanted to let you know that on MySQL I couldn't execute SUM(*). – Erotogenic

Answer 3

Aggregate function Count() always returns value

Timeconsuming

...to clarify: The "return value of the 'count' function" is ALWAYS a non-null integer, without exception. By mentioning "group by", you're referencing the containing query and changing the subject of "return value" from "count function" to "query's result set". A non-grouped count query produces a result set of a single record containing the return value of count. Alternatively, a grouped count query produces a result set where each record contains a count value. In that case, if there are no groups for count to run on, count is never run and the "query return value" is an empty set. – Fetation

Answer 4

Yes, it'll return 0 in such cases.

Intercept

Answer 5

There will always be a row of result like this:

| COUNT(*) |
------------
| 0        |

if there are no matches.

By the way, I would prefer to count only the primary key instead of *.

Sauterne

COUNT(*) is not the same as COUNT(column) in general. Counting a column checks for distinct values, not all records. Granted, when you count your primary key you'll get all records because primary keys must be unique, but this will be no faster than COUNT(*) and might be slower (unless MySQL auto-optimizes back to COUNT(*) ). – Direction

@Ty W - "Counting a column checks for distinct values". No this is not true unless you specify the distinct keyword. It counts values that are not NULL – Internuncial

Answer 6

if no record is matched the count will return 0. (so yes, count always returns a result, unless you have some syntax error)

Carbonyl

Answer 7

Yes it will return a numeric always

Conwell