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) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT(column_name) FROM table_name |
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM table_name |
SQL COUNT(DISTINCT column_name) Syntax
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:
SQL COUNT(*) Example
If we omit the WHERE clause, like this:
SELECT COUNT(*) AS NumberOfOrders FROM Orders |
The result-set will look like this:
which is the total number of rows in the table.
SQL COUNT(DISTINCT column_name) Example
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:
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.
|