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.
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;
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.
SELECT COUNT(*) FROM STUDENT;
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.
Lets take the same table STUDENT that we have taken in the above example.
SELECT COUNT(DISTINCT STU_DEPT) FROM STUDENT;
There are five distinct values of STU_DEPT in the table so the output is 5.