When working with databases, sometimes you want to find out how many rows will be returned by a query, without actually returning the results of the query. Or sometimes you might just want to find out how many rows are in a given table.
In SQL Server, you can use T-SQL‘s COUNT() function to return the number of rows that would be returned in a query.
The Data
Here’s the data we’ll use in the examples on this page:
SELECT * FROM Tasks;
Result:
TaskId TaskName TaskDescription TaskCode ------ ------------ --------------- -------- 1 Feed cats TBA null 2 Water dog TBA null 3 Feed garden TBA null 4 Paint carpet TBA null 5 Clean roof TBA null 6 Feed cats TBA null
Example – Count All Rows in a Table
Here’s an example of using the COUNT() function to return the total number of rows in a table:
SELECT COUNT(*) FROM Tasks;
Result:
6
This returns the number of rows in the table because we didn’t provide any criteria to narrow the results down.
Example – Adding Criteria
Here’s an example of counting how many rows would be returned based on a given criteria:
SELECT COUNT(*) FROM Tasks WHERE TaskName LIKE '%dog%' OR TaskName LIKE '%cat%';
Result:
3
Example – Specify a Single Column
The previous examples all use an asterisk to apply the count to all columns. You also have the option to specify a specific column.
Example:
SELECT COUNT(TaskName) FROM Tasks;
Result:
6
While this example returns the same result that we got when we used the asterisk, that isn’t necessarily always going to be the case. For example, here’s what happens if we specify a different column:
SELECT COUNT(TaskCode) FROM Tasks;
Result:
0
In this case we get zero, because that particular column contains null values in every row.
Example – Distinct
You can add the DISTINCT argument to return only the number of unique (nonnull) values.
You might’ve noticed that the every row in our TaskDescription column contains the same value (“TBA”). And the TaskName column also has a duplicate value (“Feed cats” appears twice). This could cause problems if you don’t want duplicates to be counted.
Here’s what it looks like if we apply the DISTINCT argument to the TaskName column:
SELECT COUNT(DISTINCT TaskName) FROM Tasks;
Result:
5
And here it is applied against the TaskDescription table (where all values are identical):
SELECT COUNT(DISTINCT TaskDescription) FROM Tasks;
Result:
1
Example – The HAVING Clause
You can also use COUNT() with the HAVING clause to limit a result set based on the number of rows that would be returned.
Example:
USE Music;
SELECT ar.ArtistName,
COUNT(al.AlbumName) 'Album Count'
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistName
HAVING COUNT(al.AlbumName) > 1;
Result:
ArtistName Album Count ------------------------- ----------- Allan Holdsworth 2 Devin Townsend 3 Iron Maiden 5 Michael Learns to Rock 3 Tom Jones 3
This example uses a different database than the previous examples. It returns only those artists who have released more than 1 album. We could change that number however many albums we wish:
USE Music;
SELECT ar.ArtistName,
COUNT(al.AlbumName) 'Album Count'
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistName
HAVING COUNT(al.AlbumName) > 4;
Result:
ArtistName Album Count ------------------------- ----------- Iron Maiden 5
The COUNT_BIG() Function
T-SQL also has a COUNT_BIG() function that works exactly like COUNT(), except that COUNT() returns an int data type and COUNT_BIG() returns a bigint data type.
This function can be useful for indexed views with grouped queries. You might find if you try to create an index for a view that contains COUNT(*), that you get an error like this:
Cannot create index on view "Music.dbo.AlbumsByArtist" because it uses the aggregate COUNT. Use COUNT_BIG instead.
In such cases, altering the view to use COUNT_BIG(*) should solve the problem.