SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria. SQL COUNT(column_name) Syntax The COUNT(column_name) fun...

The COUNT() function returns the number of rows that matches a specified criteria.

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name

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

SELECT COUNT(*) FROM table_name

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name)

FROM table_name

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

SQL COUNT(column_name) Example

We have the following "Orders" table:

O_Id	OrderDate	OrderPrice	Customer
1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to count the number of orders from "Customer Nilsen".

We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNilsen

FROM Orders WHERE Customer='Nilsen'

The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:

CustomerNilsen
2

If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders

FROM Orders

The result-set will look like this:

NumberOfOrders
6

which is the total number of rows in the table.

Now we want to count the number of unique customers in the "Orders" table.
We use the following SQL statement:

SELECT COUNT(DISTINCT Customer)AS

NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers
3

which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.