SQL COUNT Function

The SQL COUNT function is an aggregate function used to count rows.  Use it alone within a SELECT statement to return a count of all rows within a table, or with a GROUP BY to provide a count of rows within each group. 

Use COUNT(*) to count every record in the grouping or COUNT(expression) to count every record where expression’s result isn’t NULL.  Use DISTINCT with COUNT to find the number of unique values within a group. 

Suppose you want to find out the number of products, then use this query: 

SELECT COUNT(*) 
FROM   Production.Product 

Since the SQL Count returns a count of rows of within the results use it to return counts from a filtered query, such as the number of products whose list price is greater than $1000. 

SELECT COUNT(*) as TotalProducts FROM Production.Product WHERE ListPrice > 1000
SELECT COUNT(*) as TotalProducts
FROM   Production.Product 
WHERE  ListPrice > 1000

You can also count non-NULL column entries.  Just include the column with the COUNT function.  In the person table the Title column has NULL assigned to it.   

SQL COUNT Function

To see how many people have a title use SQL COUNT(Title): 

SELECT COUNT(*) AS TotalPeople , COUNT(title) AS PeopleWithTitles FROM person.person
SELECT COUNT(*) AS TotalPeople 
 , COUNT(title) AS PeopleWithTitles 
FROM person.person

DISCTINCT COUNT 

So far we’ve used COUNT to show total records, but you can also use it to count unique values.  You can use SQL COUNT to count distinct values.  

To do so, place DISCTINCT before the column you wish to count with the COUNT function. 

SELECT COUNT(*) AS TotalPeople , COUNT(title) AS PeopleWithTitles , COUNT(DISTINCT title) AS UniqueTitles FROM person.person;
SELECT COUNT(*) AS TotalPeople 
 , COUNT(title) AS PeopleWithTitles 
 , COUNT(DISTINCT title) AS UniqueTitles 
FROM person.person;

When used with Distinct COUNT returns a tally of Unique Values. 

USE SQL COUNT with GROUP BY 

You can also use COUNT with the GROUP BY statement to count the number of rows within each group.   

Here we group people by title and then count:

SELECT Title, COUNT(*) as TotalPeople FROM Person.Person GROUP BY Title ORDER BY TITLE
SELECT Title, COUNT(*) as TotalPeople 
FROM Person.Person 
GROUP BY Title 
ORDER BY TITLE

Additional Resources 

To learn more about SQL COUNT, check out these useful resources: 

>