SQL Server COUNT() Function

Summary: in this tutorial, you will learn how to use the SQL Server COUNT() function to get the number of items in a set.

Introduction to SQL Server COUNT() function

SQL Server COUNT() is an aggregate function that returns the number of items in a set.

The following shows the syntax of the COUNT() function:

COUNT([ALL | DISTINCT  ] expression)Code language: SQL (Structured Query Language) (sql)

In this syntax:

ALL instructs the COUNT() function to apply to all values. ALL is the default.
DISTINCT instructs the COUNT() function to return the number of unique non-null values.
expression is an expression of any type but image, text, or ntext. Note that you cannot use a subquery or an aggregate function in the expression.

The COUNT() function has another form as follows:

COUNT(*)Code language: SQL (Structured Query Language) (sql)

In this form, the COUNT(*) returns the number of rows that satisfy a certain condition. The COUNT(*) does not support DISTINCT and takes no parameters. It counts each row separately and includes rows that contain NULL values.

In summary:

COUNT(*) counts the number of items in a set. It includes NULL and duplicate values.
COUNT(ALL expression) evaluates the expression for each row in a set and returns the number of non-null values.
COUNT(DISTINCT expression) evaluates the expression for each row in a set, and returns the number of unique, non-null values.

SQL Server COUNT() function examples

The following statement creates a new table named t, insert some data into the table, and query data against it:

CREATE TABLE t(
    val INT
);

INSERT INTO t(val)
VALUES(1),(2),(2),(3),(null),(null),(4),(5);

SELECT val FROM t;Code language: SQL (Structured Query Language) (sql)

Output:

val
-----------
1
2
2
3
NULL
NULL
4
5Code language: PHP (php)

Basic SQL Server COUNT(*) example

The following example uses the COUNT(*) function to return the number of rows in the t table:

SELECT COUNT(*) val_count
FROM t;Code language: SQL (Structured Query Language) (sql)

Output:

val_count
---------
8

SQL Server COUNT(DISTINCT expression) example

The following example uses the COUNT(DISTINCT expression) to return the number of unique, non-null values in the t table:

SELECT
    COUNT(DISTINCT val) val_count
FROM
    t;Code language: SQL (Structured Query Language) (sql)

The following shows the output:

val_count
-----------
5
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row affected)Code language: PHP (php)

SQL Server COUNT( expression ) example

The following example uses the COUNT(expression) to return the number of non-null values in the t table:

SELECT
    COUNT(val)
FROM
    t;Code language: SQL (Structured Query Language) (sql)

The following shows the output:

val_count
-----------
6
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row affected)Code language: PHP (php)

Practical examples of COUNT() function

We’ll use the tables in the sample database for the demonstration.

The following statement uses the COUNT(*) function to return the number of rows in the products table:

SELECT 
    COUNT(*) product_count
FROM
    production.products;Code language: SQL (Structured Query Language) (sql)

Output:

product_count
-------------
321

(1 row affected)

The following example uses the COUNT(*) function to retrieve the number of products whose model year is 2016 and the list price is higher than 999.99:

SELECT 
   COUNT(*)
FROM 
    production.products
WHERE 
    model_year = 2016
    AND list_price > 999.99;Code language: SQL (Structured Query Language) (sql)

Output:

Result
-----------
7

(1 row affected)Code language: SQL (Structured Query Language) (sql)

Using SQL Server COUNT() function with GROUP BY clause

The following statement uses the COUNT(*) function to find the number of products in each product category:

SELECT 
    category_name,
    COUNT(*) product_count
FROM
    production.products p
    INNER JOIN production.categories c 
    ON c.category_id = p.category_id
GROUP BY 
    category_name
ORDER BY
    product_count DESC;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

category_name        product_count
-------------------- -------------
Cruisers Bicycles    78
Mountain Bikes       60
Road Bikes           60
Children Bicycles    59
Comfort Bicycles     30
Electric Bikes       24
Cyclocross Bicycles  10

(7 rows affected)

In this example, first, the GROUP BY clause divided the products into groups using category names then the COUNT() function is applied to each group.

Using SQL Server COUNT() with HAVING clause example

The following statement returns the brand and the number of products for each. In addition, it returns only the brands that have the number of products greater than 20:

SELECT 
    brand_name,
    COUNT(*) product_count
FROM
    production.products p
    INNER JOIN production.brands c 
    ON c.brand_id = p.brand_id
GROUP BY 
    brand_name
HAVING
    COUNT(*) > 20
ORDER BY
    product_count DESC;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

brand_name           product_count
-------------------- -------------
Trek                 135
Electra              118
Surly                25
Sun Bicycles         23

(4 rows affected)

Summary

Use the COUNT(*) to retrieve the number of rows in a table.
Use the COUNT(ALL expression) to count the number of non-null values.
Use the COUNT(DISTINCT expression) to obtain the number of unique, non-null values.

Was this tutorial helpful?