SQL Server COUNT(*) Function

Summary: in this tutorial, you will learn how to use the SQL Server COUNT(*) to obtain the number of rows that meet certain criteria.

Introduction to the SQL Server COUNT(*) function

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

The COUNT(*) is a form of the COUNT() function that returns the total number of rows that meet certain criteria.

In practice, you often use the COUNT(*) to count rows in a table, whether any columns contain NULL or duplicate values or not.

Here’s the basic syntax of the COUNT(*) function:

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

The query may include other clauses such as JOIN, WHERE, GROUP BY, and HAVING clauses.

SQL Server COUNT(*) function examples

Let’s take some examples of using the SQL Server COUNT(*) function.

We’ll use the production.products and production.brands tables from the sample database for the demonstration:

1) Basic SQL Server COUNT(*) function example

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

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

Output:

product_count
-------------
321Code language: SQL (Structured Query Language) (sql)

The output indicates that the products table has 321 rows.

2) Using the COUNT(*) function with a condition

The following example uses the COUNT(*) to retrieve the number of products whose list prices are greater than 900:

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

Output:

product_count
-------------
139Code language: SQL (Structured Query Language) (sql)

In this example:

• The WHERE clause includes only products whose list price is greater than 900.
• The COUNT(*) returns the number of filtered rows.

3) Using the COUNT(*) function with JOIN clause example

The following example uses the COUNT(*) to retrieve the total number of products with the brand “Electra”:

SELECT 
  COUNT(*) product_count 
FROM 
  production.products p 
  INNER JOIN production.brands b ON b.brand_id = p.brand_id 
WHERE 
  b.brand_name = 'Electra';Code language: SQL (Structured Query Language) (sql)

Output:

product_count
-------------
118Code language: SQL (Structured Query Language) (sql)

In this example:

• First, join the production.products table with the production.brands table using the values in the brand_id column.
• Second, filter only products with the brand name "Electra" using a WHERE clause.
• Third, return the number of rows using the COUNT(*) function.

The output indicates that there are 118 rows in the products table with the brand "Electra".

4) Using the COUNT(*) function with GROUP BY clause

The following example uses the COUNT(*) function with the GROUP BY clause to return the number of products per brand:

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

Output:

brand_name   | product_count
-------------+------------
Electra      | 118
Haro         | 10
Heller       | 3
Pure Cycles  | 3
Ritchey      | 1
Strider      | 3
Sun Bicycles | 23
Surly        | 25
Trek         | 135
(9 rows)Code language: SQL (Structured Query Language) (sql)

In this example:

• First, join the production.products table with the production.brand table by matching values in the brand_id column.
• Second, group rows by brand names using the GROUP BY clause.
• Third, count the number of rows for each group.

5) Using the COUNT(*) function with HAVING clause

The following example uses the COUNT(*) function in the HAVING clause to retrieve the brands that have more than 100 products:

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

Output:

brand_name | product_count
-----------+------------
Electra    | 118
Trek       | 135
(2 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the COUNT(*) function to get the number of rows that satisfy a certain condition.