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:

COUNT() FunctionCount DuplicatesCount NULL values
COUNT(*)YesYes
COUNT(DISTINCT column)NoNo
COUNT(ALL column)YesNo

SQL COUNT(*) function examples

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

Orders Table

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;
SQL COUNT example

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 WHERE clause

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;
SQL COUNT with GROUP BY clause

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;
SQL COUNT HAVING clause

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.

Customers Table

To count all customers, you use the following query:

SELECT COUNT(*) FROM customers;
SQL COUNT customers

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

SELECT COUNT(ALL country) FROM customers;
SQL COUNT ALL example

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;
SQL COUNT DISTINCT example

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.