COUNT (SQL Aggregate Function)

Returns the number of items in a group. The COUNT function returns the total number of rows that match a specified condition. It is an aggregate function that counts the number of rows in a table or the number of non-NULL values in a column.

Syntax

COUNT(expression)

Or, to count all rows:

COUNT(*)

Parameters

expression
An expression that evaluates to a value from a column. COUNT(expression) counts the number of rows where the specified expression is not NULL.

Return Value

Returns an integer representing the count of rows or non-NULL values.

Description

The COUNT aggregate function is used to retrieve the number of rows that satisfy a given condition. It can be used in several ways:

Note: If a column contains NULL values, COUNT(column_name) will not count those rows, while COUNT(*) will.

Examples

Example 1: Counting all rows in a table

To count the total number of employees in the Employees table:

SQL Query

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

Result (example)

TotalEmployees
--------------
273

Example 2: Counting rows with a specific condition

To count the number of employees in the 'Sales' department:

SQL Query

SELECT COUNT(*) AS SalesEmployees
FROM Employees
WHERE Department = 'Sales';

Result (example)

SalesEmployees
--------------
35

Example 3: Counting non-NULL values in a column

To count the number of employees who have a specified ManagerID (i.e., not NULL):

SQL Query

SELECT COUNT(ManagerID) AS EmployeesWithManagers
FROM Employees;

Result (example)

EmployeesWithManagers
---------------------
250

Example 4: Counting distinct values

To count the number of unique departments:

SQL Query

SELECT COUNT(DISTINCT Department) AS NumberOfDepartments
FROM Employees;

Result (example)

NumberOfDepartments
-------------------
15

Example 5: Using COUNT with GROUP BY

To count the number of employees in each department:

SQL Query

SELECT Department, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY Department
ORDER BY NumberOfEmployees DESC;

Result (example)

Department        NumberOfEmployees
----------------- -----------------
Sales             35
Engineering       28
Human Resources   22
Marketing         19
...

See Also