SQL COUNT and SQL COUNT

By: Manvendra Singh | Updated: 2022-07-13 | Comments | Related: > Functions System

Problem

Often there is a need to count a set of records from a table in a SQL query or stored procedure. SQL Server offers two functions that can be used to calculate the count of rows. Read this SQL tutorial to learn and understand more about these SQL Server functions.

Solution

SQL Server offers two functions COUNT and COUNT_BIG which can get a count of rows. Both functions can be used with a SELECT statement including WHERE, HAVING, GROUP BY, and ORDER BY clauses.

The COUNT function will return the result as an int (4 bytes) whereas the COUNT_BIG function will return the output as a bigint (8 bytes). The only difference is how large the value can be when counting using an int versus a bigint data type.

The syntax of these SQL functions is below:

-- Aggregation Function Syntax 
 
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )  

COUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )

ALL - will count all records including duplicates. ALL is the default.
DISTINCT - will count unique values and duplicate values are only counted once.
* - this will return the count of all rows including NULL values.
expression - is the column name on which you want to perform the count.

Note: NULL values are ignored and not counted unless you use *.

Simple Use Case for SQL COUNT and COUNT_BIG Functions

Let's start with a simple use case where I will pass an expression (column name) to the function along with ALL and DISTINCT to see the results.

SELECT COUNT(ProductName),
       COUNT(ALL ProductName) AS [with ALL],
       COUNT(DISTINCT ProductName) AS [With DISTINCT] -- DISTINCT keyword
FROM [dbo].OrderDetails]

The output is below. The first two columns count 28 rows, whereas the 3rd column using DISTINCT counts 10 rows. This is 10 because column ProductName has only 10 unique values and the rest are duplicates.

There is one more argument using which we can get the total number of rows in a table using * in the functions.

SELECT COUNT(*)
FROM [dbo].[OrderDetails]

Below are the results which are similar to the counts above.

Now we can take a look at COUNT_BIG to see the output.

SELECT COUNT_BIG(*) AS [Product Sales Count],
       COUNT_BIG(ALL ProductName) AS [with ALL],
       COUNT_BIG(DISTINCT ProductName) AS [With Distinct]
FROM [dbo].OrderDetails]

The output is the same as using COUNT.

A closer look at NULL values when using COUNT and COUNT_BIG

The below code will show the differences of the results when there are NULL values in the table. We will create a table, insert some data and see the results.

CREATE TABLE [dbo].[test]([testCol] [nchar](10) NULL) ON [PRIMARY]
GO

INSERT INTO [dbo].[test]
VALUES (1),(NULL),(NULL),(1),(2)
GO

SELECT COUNT(*)                  AS allRows, 
       COUNT(ALL [testCol])      AS allRowsExcludingNULL, 
       COUNT(DISTINCT [testCol]) AS allDistinctRowsExcludingNULL, 
       COUNT([testCol])          AS alltestColRowsExcludingNULL
FROM [dbo].[test]

This will return the following result set. We can see that even though we have NULL values for 2 rows, when using * we still get a count of all 5 rows. ALL and expression show 3 and DISCTINCT returns 2.

Using SQL COUNT Function with other SQL Aggregate Functions

The SQL function COUNT is an aggregate function that can also be used with other aggregate functions like AVG, SUM, etc. Here, I will show use cases in which I will pull the total number of products sold, their average price, and total sales using all these aggregate functions together.

SELECT COUNT(*) AS [Total Product Sales],
       AVG(Price) AS [Avg Price],
       SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]

Here, we can see the result set that indicates a total of 28 products were sold with an average price of $1755.82, and the total sales of all products are $49163.00.

Using SQL COUNT with SQL GROUP BY statement

Now we want to see a similar view for each product. We will use the GROUP BY statement on the ProductName column to group the same products and see total sales numbers. The GROUP BY statement groups rows with the same values in a summary row.

SELECT ProductName,
       COUNT(ProductName) AS [Total Product Sales],
       AVG(Price) AS [Avg Price],
       SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY ProductName

Below is the output:

Here is a similar query, but this time grouping by city.

We can also use COUNT_BIG to get the same results.

SELECT City,
       COUNT_BIG(ProductName) AS [Total Product Sales],
       AVG(Price) AS [Avg Price],
       SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY City

Here are the results, which match the above results.

Using SQL COUNT with HAVING clause

This section explains using COUNT and COUNT_BIG with a HAVING clause. Suppose you want to get a list of sales by cities where total sales were greater than 5 for a product.

SELECT City,
       COUNT(ProductName) AS [Total Product Sales],
       AVG(Price) AS [Avg Price],
       SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY City
HAVING COUNT(ProductName) > 5

The result shows 3 rows where the total product sales is greater than 5.

Here is the same example using COUNT_BIG.

Using SQL COUNT with ORDER BY statement

This section will explain using COUNT and COUNT_BIG with ORDER BY. Suppose you want to get a result based on a specific order. In the following query, we will order the results by total product sales.

SELECT City,
       COUNT(ProductName) AS [Total Product Sales],
       AVG(Price) AS [Avg Price],
       SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY City
ORDER BY [Total Product Sales]

Here are the results:

Here is another example using COUNT_BIG in the following SQL statement:

SELECT City,
       COUNT_BIG(*) AS [Product Sales Count],
       AVG(Price) AS [Avg Price],
       SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY City
ORDER BY [Total Sales] DESC

Next Steps

This tutorial has explained how to use SQL Server functions COUNT and COUNT_BIG to get a count of rows. Use these examples as well as build upon the examples we showed with your database tables.
Check out these additional resources:

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

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

View all my tips

Article Last Updated: 2022-07-13

Getting Starting with SQL COUNT() and COUNT_BIG() Functions