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.
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.
To see how many people have a title use SQL COUNT(Title):
DISTINCT 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.
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:
Additional Resources
To learn more about SQL COUNT, check out these useful resources:
