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(*)

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)

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)

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;

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: PHP (php)

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;

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;

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;

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

SELECT 
    COUNT(ALL country)
FROM
    customers;

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;

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.