SQL COUNT() Function

COUNT() function returns the number of rows that match the specified condition.

SQL COUNT() syntax

SQL COUNT(column_name) syntax

COUNT(column_name) function returns the number of values in the specified column (NULL is not counted):

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) syntax

COUNT(*) function returns the number of records in the table:

SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) syntax

COUNT(DISTINCT column_name) function returns the number of different values in the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

Note:COUNT(DISTINCT) is applicable to ORACLE and Microsoft SQL Server, but cannot be used in Microsoft Access.

SQL COUNT(column_name) Example

We have the following 'Orders' table:

O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter

Now, we want to calculate the number of orders for customer 'Carter'.

We use the following SQL statement: 

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'

The result of the above SQL statement is 2 because customer Carter has 2 orders:

CustomerNilsen
2

SQL COUNT(*) Example

If we omit the WHERE clause, for example: 

SELECT COUNT(*) AS NumberOfOrders FROM Orders

The result set is similar to this:

NumberOfOrders
6

This is the total number of rows in the table.

SQL COUNT(DISTINCT column_name) Example

Now, we want to calculate the number of different customers in the 'Orders' table.

We use the following SQL statement:

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result set is similar to this:

NumberOfCustomers
3

This is the number of different customers (Bush, Carter, and Adams) in the 'Orders' table.