SQL - COUNT() Function

The COUNT() function is an aggregate function that is used to find the number of values in the specified column excluding NULL values. It can be applied on numeric, character, or date values.


SELECT COUNT([ALL | DISTINCT] expression | column_name)
FROM table_name
[WHERE condition]
[GROUP BY]    ;

For the demo purpose, we will use the following Employee table.

EmpId FirstName LastName Email Salary DeptId
1 John King '[email protected]' 24000 10
2 James Bond 17000 20
3 Neena Kochhar '[email protected]' 15000 20
4 Lex King '[email protected]' 9000 30
5 Amit Patel 60000 30
6 Abdul Kalam '[email protected]' 4800 40

Specify the column name to count the total values in that column excluding NULL values. The following counts the total records in the FirstName column.

SQL Script: COUNT()
SELECT COUNT(FirstName) AS "Total" FROM Employee;

The COUNT() function excludes the NULL values.

SQL Script: COUNT()
SELECT COUNT(Email) AS "Total" FROM Employee;

The COUNT() function can contain other functions such as Distinct, as shown below.

SQL Script: COUNT()
SELECT COUNT(Distinct(LastName)) AS "Total" FROM Employee;

Use the COUNT(*) to count the total records in the table. The following query count the total records of the Employee table.

SQL Script: COUNT(*)
SELECT COUNT(*) AS "Total Records" FROM Employee;
Total Records

The COUNT() is an aggregate function, so it can be used in Group By queries. The following query counts the number of employees in each department.

SQL Script: COUNT() with Group By
SELECT DeptId, COUNT(*) AS "Total Employees" 
FROM Employee
DeptId Total Employees
10 1
20 2
30 2
40 1