SQL SELECT COUNT

The count() function returns the number of rows that matches the given condition.

SQL COUNT() function Syntax

SELECT COUNT (column_name)  
FROM table_name 
WHERE condition;

SQL SELECT COUNT(column_name) Example

SELECT COUNT(column_name) counts the non-null values of column in the table.

Table: STUDENT

STU_ID      STU_NAME      STU_AGE     STU_DEPT
------      --------      --------    --------
1001         Steve          28         CSE
1002         Chaitanya      29         CSE
1003         Rick           27         ECE
1004         Ajeet          28         IT
1005         Robert         26         ME
1006         David          30         CL
1007         Lucy           30         null

Select Count Query:

SELECT COUNT(STU_DEPT)
FROM STUDENT;

Result:

6

The total number of STU_DEPT values in above table are 7 but one of them is null. Since count(column_name) counts non-null values of the given column, thus the output is 6.

SQL SELECT COUNT(*)

SELECT COUNT(*) counts the number of rows in the table.

Consider the same STUDENT table that we have seen in the above example.

Query:

SELECT COUNT(*)
FROM STUDENT;

Output:
7

SQL SELECT COUNT(DISTINCT column_name)

SELECT COUNT(DISTINCT column_name) counts the total number of distinct values of column in the table. Refer this guide – SQL DISTINCT to learn more about SQL SELECT DISTINCT statement.

Query:
Lets take the same table STUDENT that we have taken in the above example.

SELECT COUNT(DISTINCT STU_DEPT)
FROM STUDENT;

Output:
5

There are five distinct values of STU_DEPT in the table so the output is 5.