COUNT() aggregate function returns the number of rows in a result set of a
The result provided by
COUNT() is a
If there are no matching rows, the returned value is
This tells us that there are 204 records in the
actor table of the
If we add a
WHERE clause, we can narrow down the result set. Like this:
The above example tells us that there are 10 records where the value of the
first_name field begins with the letter "B".
You can see that if we remove the
COUNT(*) function, we end up with a list of all records returned. By counting the number of rows, we see that 10 records were returned.
COUNT() function can be used in different ways. You may get different results depending on how you use it, so it pays to understand how it works.
Returns the number of rows that contain non-
The following example retrieves all records where the
last_name field does not contain a
Returns the number of rows in a result set whether or not they contain
The following example contains all records, even if some fields contain a
NULL value. So if some actors don't have a last name recorded in the table, this statement will return a higher number than the previous example.
DISTINCT keyword removes duplicate records. Therefore, this returns the number of unique rows that do not contain
The following example returns a count of unique last names from the table. If a last name is shared by two or more actors, the result will be a lower number than the above examples.
You can use
COUNT() with the
GROUP BY clause to provide a count of records within each group.
The above example groups all last names and provides the count of each one. For example, we can see that three actors have
ALLEN as their last name, only one has
You can add the
HAVING clause to the
GROUP BY clause to filter the results further.
Here we add a
HAVING clause with another
COUNT() function to return only those records that have a count greater than 3:
You can combine
COUNT() with flow control functions for greater functionality. For example, you could use the
IF() function as part of the expression to be used in the
COUNT() function. Doing this can be quite helpful for providing a quick breakdown of the data within a database.
Consider this data in the
You can see that there's a field called
length, and each film has its length recorded in that field.
So we could use that field to do the following:
The above statement separates the results into three groups;
Long, depending on the length of the film. We use the
IF() function to compare the length of the film to a given expression, and depending on the result, it returns a
1 or a
NULL. If it returns a
1, it will be counted under the column heading that we name (
The examples on this page use the Sakila sample database.