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

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:

Department NumEmployee
Finance 3
Development 2
NULL 2

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>