SQL SELECT COUNT

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

SQL COUNT Syntax

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.

SQL COUNT Examples

Table: Employees

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

SELECT COUNT(*) AS NumEmployee
FROM Employees

The result will be 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:

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:

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:

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: