Count of rows with the SQL Server COUNT Function


By:   |   Updated: 2016-07-07   |   Comments (3)   |   Related: More > 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

SQL Server COUNT Function with Group By

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 WHERE and Group By

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 Group By and Having

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 Distinct

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

SQL Server COUNT Function with Partitioning

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


Last Updated: 2016-07-07


get scripts

next tip button



About the author
MSSQLTips author Tim Wiseman Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, December 02, 2016 - 10:09:07 AM - Kris Maly Back To Top

I enjoyed reading this post.

I found you have not dealt with COUNT Function and NULL values. It is an important as pect in count.

Say you are counting from a column if there are Nulls then COUNT function wount count.

If I am wrong please feel free to let me know.

 

Thanks for educating the community and appreciate your volunteership.

 

Thanks a bunch

Happy Holidays!


Wednesday, July 13, 2016 - 8:45:14 PM - Timothy A. Wiseman Back To Top

Hello MECDallas,

Thank you for the feedback.  I have heard many times that using count(1) is more effecient than using count(*).  I suspect it was true in some previous version, but I tested it in SQL Server 2008 R2 and the two queries generate the same execution plan and have the same response time.


Tuesday, July 12, 2016 - 7:31:43 PM - MECDallas Back To Top

 use COUNT(1) instead of *

You don't need to pull all the columns out of a table to get its count

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools