In SQL, the COUNT() function is an aggregate function that returns the number of items found in a group.
You can use COUNT() in multiple parts of a query. For example, you can use it in the SELECT list, or the HAVING clause when filtering groups.
Sample Table
Suppose we have the following table:
SELECT * FROM Pets;Result:
+---------+-------------+-----------+-----------+------------+ | PetId | PetTypeId | OwnerId | PetName | DOB | |---------+-------------+-----------+-----------+------------| | 1 | 2 | 3 | Fluffy | 2020-11-20 | | 2 | 3 | 3 | Fetch | 2019-08-16 | | 3 | 2 | 2 | Scratch | 2018-10-01 | | 4 | 3 | 3 | Wag | 2020-03-15 | | 5 | 1 | 1 | Tweet | 2020-11-28 | | 6 | 3 | 4 | Fluffy | 2020-09-17 | | 7 | 3 | 2 | Bark | NULL | | 8 | 2 | 4 | Meow | NULL | +---------+-------------+-----------+-----------+------------+
The following examples will use the COUNT() function when querying this table.
Example
Here’s a simple example to get started.
SELECT COUNT(*) AS Count
FROM Pets;Result:
+---------+ | Count | |---------| | 8 | +---------+
This tells us that there’s 8 rows in the table. We know that because we used the asterisk wildcard (*) to specify all rows and all columns.
Count a Specific Column
You can also specify a particular column to count. The COUNT() function only counts non-NULL results, so if you specify a column that contains NULL values, those values won’t be counted.
Here’s an example to demonstrate what I mean.
SELECT COUNT(DOB) AS Count
FROM Pets;Result:
+---------+ | Count | |---------| | 6 | +---------+ Warning: Null value is eliminated by an aggregate or other SET operation.
In this case, the Pets table contains two NULL values in the DOB column (two pets haven’t supplied their date of birth), and so COUNT(DOB) returns 6, instead of 8 when we used COUNT(*).
The reason COUNT(*) in the previous example returned all rows, is because those two rows did have data in the other columns.
In my example, my DBMS also returned a warning about this. You may or may not get a warning, depending on your DBMS and your specific configuration.
Filtered Results
The COUNT() function counts the rows returned by the query. So if you filter the results, the result of COUNT() will reflect that.
SELECT COUNT(*) AS Count
FROM Pets
WHERE PetName = 'Fluffy';Result:
+---------+ | Count | |---------| | 2 | +---------+
In this case, there are two pets with a name of Fluffy. Therefore, two rows would have been returned by the query, and the result of COUNT() is 2.
Count Distinct Columns
By default, the COUNT() function includes an implicit ALL keyword. This means that it includes duplicates in its results.
But you also have the option of adding the DISTINCT keyword to specify that just distinct values are returned. That is, you can specify that it excludes duplicates.
Let’s choose the PetName column. If you look at the original table above, you can see that the PetName column includes two rows with the same value (Fluffy).
First we’ll run a COUNT(ALL PetName) query to include all duplicate values in the count:
SELECT COUNT(ALL PetName) AS Count
FROM Pets;Result:
+---------+ | Count | |---------| | 8 | +---------+
So, there are eight rows. Remember that this is the same result we would get if we hadn’t included the ALL keyword, because ALL is the default.
Now we’ll run a COUNT(DISTINCT PetName) to eliminate any duplicates from the count.
SELECT COUNT(DISTINCT PetName) AS Count
FROM Pets;Result:
+---------+ | Count | |---------| | 7 | +---------+
This time the result is 7. This is because our duplicate value was eliminated. That is, the duplicate value was treated as if there was only one value.
Using COUNT() with the HAVING Clause
You can include the COUNT() function in multiple parts of a query. It’s not just limited to the SELECT list.
Here’s an example that uses COUNT() in both the HAVING clause and the SELECT list.
SELECT
PetTypeId,
COUNT(PetTypeId) AS Count
FROM Pets
GROUP BY PetTypeId
HAVING COUNT(PetTypeId) > 2
ORDER BY Count DESC;Result:
+-------------+---------+ | PetTypeId | Count | |-------------+---------| | 3 | 4 | | 2 | 3 | +-------------+---------+
In this case, we used the HAVING clause in conjunction with the GROUP BY clause to return just those rows that have a COUNT(PetTypeId) of greater than 2.
You’re not limited to just the =, <, >=, IN, LIKE, etc).
See SQL Operators for a list of operators available in SQL.
Window Functions
Depending on your DBMS, you may be able to use an OVER clause with your COUNT() function to create a window function.
A window function performs an aggregate-like operation on a set of query rows. It produces a result for each query row. This is in contrast to an aggregate operation, which groups query rows into a single result row.
Here’s an example to demonstrate the concept.
We’ve already seen the Pets table. Our database also has an Owners table, and it contains the following data:
+-----------+-------------+------------+----------------+-------------------+ | OwnerId | FirstName | LastName | Phone | Email | |-----------+-------------+------------+----------------+-------------------| | 1 | Homer | Connery | (308) 555-0100 | [email protected] | | 2 | Bart | Pitt | (231) 465-3497 | [email protected] | | 3 | Nancy | Simpson | (489) 591-0408 | NULL | | 4 | Boris | Trump | (349) 611-8908 | NULL | | 5 | Woody | Eastwood | (308) 555-0112 | [email protected] | | 6 | Burt | Tyson | (309) 565-0112 | [email protected] | +-----------+-------------+------------+----------------+-------------------+
We can retrieve data from these tables and present them as one result set by using a join.
We can also use the COUNT() function with the OVER clause to apply a window function to the data.
SELECT
CONCAT(o.FirstName, ' ', o.LastName) AS Owner,
p.PetName,
COUNT(PetId) OVER (PARTITION BY CONCAT(o.FirstName, ' ', o.LastName)) AS "Number of pets from this owner"
FROM Owners o
INNER JOIN Pets p
ON p.OwnerId = o.OwnerId
ORDER BY "Number of pets from this owner" DESC;Result:
+---------------+-----------+----------------------------------+ | Owner | PetName | Number of pets from this owner | |---------------+-----------+----------------------------------| | Nancy Simpson | Wag | 3 | | Nancy Simpson | Fluffy | 3 | | Nancy Simpson | Fetch | 3 | | Bart Pitt | Scratch | 2 | | Bart Pitt | Bark | 2 | | Boris Trump | Meow | 2 | | Boris Trump | Fluffy | 2 | | Homer Connery | Tweet | 1 | +---------------+-----------+----------------------------------+
In this case we used the OVER clause with our COUNT() clause to partition the count to just the owner’s name.
The result is that owners with multiple pets are displayed on multiple rows (because each pet also has to be displayed), and each row contains the aggregate count of that owner’s pets.
This concept can also be applied to other aggregate functions in SQL, such as SUM(), MIN(), MAX(), and AVG().
COUNT_BIG()
If you’re counting seriously large data sets in SQL Server, you might find that the COUNT() function produces an error, due to the number being too high. This will only happen your count is greater than 2,147,483,647.
In such cases, you can use COUNT_BIG(), which can cater for much larger numbers.
See How COUNT_BIG() Works in SQL Server and COUNT() vs COUNT_BIG() for a more detailed explanation.
ANSI SQL Standard
The COUNT() function is listed in the SQL standard, and it is available in most (if not all) of the major DBMSs, and works pretty much the same across them.
For code examples done in various DBMSs, see SQLite COUNT(), SQL Server COUNT(), and MySQL COUNT().