Count of rows with the SQL Server COUNT Function

By: Tim Wiseman   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Functions System

Problem

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?

Solution

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

select @@ROWCOUNT

Next Steps

• 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

Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips