SQL COUNT Function

Summary: in this tutorial, you will learn how to use the SQL COUNT function to get the number of rows in a specified table.

Introduction to SQL COUNT function

The COUNT() function returns the number of rows in a group. The first form of the COUNT()function is as follows:

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

The  COUNT(*) function returns a number of rows in a specified table or view that includes the number of duplicates and NULL values.

To return the number of rows that excludes the number of duplicates and NULL values, you use the following form of the  COUNT() function:

COUNT(DISTINCT column)Code language: SQL (Structured Query Language) (sql)

To return the number of rows that includes the number of duplicates and excludes the number of the NULL values, you use the following form of the  COUNT() function:

COUNT(ALL column)Code language: SQL (Structured Query Language) (sql)

The following table illustrates all forms of the  COUNT() function:

SQL COUNT(*) function examples

We will use the orders table in the sample database in the following  COUNT(*) function examples.

Simple SQL COUNT(*) example

To get the number of orders in the orders table, you use the  COUNT(*) function as follows:

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

The pending order is the order whose shipped date is NULL. To get the number of pending orders, you use the following query:

SELECT 
    COUNT(*) 'Pending orders'
FROM
    orders
WHERE
    shippeddate IS NULL;Code language: SQL (Structured Query Language) (sql)

SQL COUNT(*) with GROUP BY clause example

To get the number of orders by customers, you use the  COUNT(*) function with the GROUP BY clause as the following query:

SELECT 
    customerid, COUNT(*)
FROM
    orders
GROUP BY customerid
ORDER BY COUNT(*) DESC;Code language: SQL (Structured Query Language) (sql)

The  GROUP BY clause is used to group the orders by customers. For each group, the  COUNT(*) function counts the orders by customer.

SQL COUNT(*) with HAVING clause example

To get customers who have more than 20 orders, you use the  COUNT(*) function with  GROUP BY and HAVING clauses as the following query:

SELECT 
    customerid, COUNT(*)
FROM
    orders
GROUP BY customerid
HAVING COUNT(*) > 20;Code language: SQL (Structured Query Language) (sql)

The  GROUP BY clause divides the orders into groups by customerid. The  COUNT(*) function returns the number of orders for each customerid. The HAVING clause gets only groups that have more than 20 orders.

SQL COUNT ALL example

Let’s take a look at the customers table.

To count all customers, you use the following query:

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

The following query returns the number of countries except for the NULL values:

SELECT 
    COUNT(ALL country)
FROM
    customers;Code language: SQL (Structured Query Language) (sql)

SQL COUNT DISTINCT example

To exclude both NULL values and duplicates, you use the  COUNT(DISTINCT column) as the following query:

SELECT 
    COUNT(DISTINCT country)
FROM
    customers;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use a various form of the SQL COUNT function that returns the number of rows in a specified table.