Count of rows with the SQL Server COUNT Function
What is the best way to find the number of rows in a SQL Server table? What are various options in SQL Server to get a count of rows? Can you provide any examples?
There are several ways to get the count of records for a SQL Server table and we will look at a few options in this tip.
Basic Usage of SQL Server COUNT Function
COUNT is an aggregate function in SQL Server which returns the number of items in a group. COUNT will always return an INT. COUNT will use indexes, but depending on the query can perform better with non-clustered indexes than with clustered indexes.
It is easiest to look at it through examples, so I will start with a rather contrived sample table like:
create table dbo.employees ( Id int identity(1, 1) primary key, FName varchar(25), LName varchar(25), Title varchar(25), PrimaryPhone varchar(12), OfficeLoc varchar(2) );
insert into dbo.employees (FName, LName, Title, PrimaryPhone, OfficeLoc) values ('Albert', 'Einstein', 'Physicist', '299-792-4580', 'OK'), ('Archimedes', 'Syracuse', 'Mathematician', '314-159-2653', 'NV'), ('Pythagoras', 'Samos', 'Mathematician', '141-421-3562', 'NV'), ('Roger', 'Penrose', 'Mathematician', '161-803-3988', 'NV'), ('Amedo', 'Avogadro', 'Chemist', '602-214-0857', 'NV'), ('Michael', 'Faraday', 'Physicist', '965-853-3289', 'NV'), ('Isaac', 'Newton', 'Physicist', '667-408-3100', 'NV'), ('Neil', 'Tyson', 'Astrophysicist', '195-810-0500', 'NY'), ('Emmy', 'Noether', 'Mathematician', '188-203-2300', 'NV');
-- this will return a value of 9 select count(*) from dbo.employees
Using COUNT in its simplest form, like: select count(*) from dbo.employees simply returns the number of rows, which is 9.
SQL Server COUNT Function with Group By
COUNT is more interestingly used along with GROUP BY to get the counts of specific information. If we wanted to know the number of each job title or position, we could use:
select Title, count(*) as PositionCount from dbo.employees group by title
Naturally, you can have several columns specified in the GROUP BY clause. So, if we wanted to know the number of each job type in Nevada (NV) specifically, we could put both Title and OfficeLoc in the GROUP BY clause and use a WHERE clause to limit it to Nevada:
select Title, OfficeLoc, count(*) as PositionCount from dbo.employees where OfficeLoc = 'NV' group by Title, OfficeLoc
SQL Server COUNT Function with Group By and Having
If we want to limit the result set based on COUNT, or any other aggregate function, we can do that through the HAVING clause. So, if we want a list of job titles that are shared by three or more people:
select Title, count(*) as PositionCount from dbo.employees group by Title having count(*) >= 3
SQL Server COUNT Function with Distinct
We can use COUNT with the DISTINCT argument to have it only return the number of different values in the selected row. It cannot be used with * and must have an expression (such as a column or list of columns) specified. So, if we wanted to know the number of different job titles used at each location, we could use:
select OfficeLoc, count(distinct Title) as NumJobTypes from dbo.employees group by OfficeLoc
SQL Server COUNT Function with Partitioning
We could also use an OVER clause to partition the results. For instance, to get the number of employees per location with an over clause, instead of a group by clause, we could use:
select distinct OfficeLoc, count(LName) over (partition by OfficeLoc) as NumEmployees from dbo.employees
Related SQL Server COUNT Function Options
COUNT_BIG is an almost identical function that will always return a bigint value. Its usage is essentially the same as COUNT other than being able to deal with larger results.
@@rowcount is also in some ways related and returns the number of rows affected by the last statement. So executing the below command will give a result of 9 for the @@rowcount.
select LName from dbo.employees
- The MSDN page covering COUNT is available here
- The MSDN page covering COUNT_BIG is available here
- The SQL Server T-SQL Aggregate Functions is a closely related tip
About the author
View all my tips