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.




No comments:

Post a Comment