SQL SELECT COUNT – SQL Tutorial

The SQL COUNT function returns the number of rows in a query. NULL value will not be counted.

SELECT COUNT(expression) AS resultName
FROM tableName
WHERE conditions

The expression can be *, column name or DISTINCT column name. All these 3 expressions work with MS SQL Server, Oracle and mySQL.

Table: Employees

EmployeeId	FirstName	LastName	Department	Salary
203	Mary	Fox	Finance	78000
204	Joe	Lanyon	Finance	45800
205	Sally	Daff	Finance	57000
206	Grace	Salter		62000
302	James	Band		75000
303	Dona	Earl	Development	55000
304	Tony	Oakes	Development	49000

Simple Example
We want to know how many employees in the table:

SELECT COUNT(*) AS NumEmployee
FROM Employees

The result will be 7:

NumEmployee
7

Example of NULL Column
If we use the following statement:

SELECT COUNT(Department) AS NumDepartment
FROM Employees

The result will be 5, because 2 rows in Employees table have NULL value of column Department:

NumDepartment
5

Example of SELECT COUNT(DISTINCT Column Name)
If we want to select the number of unique departments:

SELECT COUNT(DISTINCT Department) AS NumDepartment
FROM Employees

The result will look like:

NumDepartment
2

Example of using WHERE Clause
Select how many employees have salary over $50,000:

SELECT COUNT(EmployeeId) AS NumEmployee
FROM Employees
WHERE Salary > 50000

The result will look like:

NumEmployee
5

Example of using GROUP BY
Sometime, we may want to know how many employees in each department:

SELECT Department, COUNT(EmployeeId) AS NumEmployee
FROM Employees
GROUP BY Department

The result will look like: