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 "Result" table:

Student_No	Subject_No	Year_Exam	Marks
ST101	SU03	1	69
ST102	SU01	1	61
ST101	SU04	1	70
ST101	SU05	1	87
ST103	SU01	2	51
ST103	SU03	1	59
ST103	SU01	3	56
ST108	SU01	1	78
ST105	SU05	2	98

Now we want to count the number of Student from "Year_Exam=1".

We use the following SQL statement:

SELECT COUNT(Student_No) FROM result WHERE Year_Exam=1;

The result-set will look like this:

COUNT(Student_No)

6

SQL COUNT(*) Example

If we omit the WHERE clause, like this:

SELECT COUNT(Student_No) AS Number_Of_Student FROM result;

The result-set will look like this:

Number_Of_Student

9

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 Student in the "result" table.

We use the following SQL statement:

SELECT COUNT(DISTINCT Student_No) AS Number_Of_Student FROM result;

The result-set will look like this:

Number_Of_Student

5

which is the number of unique Student_No (ST101,ST102,ST103,ST105,ST108) in the "result" table.