The COUNT function in SQL is used to count the number of rows returned by a query. It can be applied to a specific column, or used without arguments to count all rows in a result set.
Syntax
The COUNT function has two main variations:
Count All Rows: Count all rows in the result set.
SELECT COUNT(*)
FROM table_name;
Count Specific Column: Count the number of non-NULL values in a specific column.
SELECT COUNT(column_name)
FROM table_name;
Example
Consider a table employees with columns employee_id, first_name, and department_id.
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
department_id INT
);
INSERT INTO employees (employee_id, first_name, department_id)
VALUES
(1, 'John', 1),
(2, 'Jane', 2),
(3, 'Alice', 1),
(4, 'Bob', NULL),
(5, 'Emily', 2);
To count the total number of employees:
SELECT COUNT(*)
FROM employees;
Output
| COUNT(*) |
|------------|
| 5 |
To count the number of employees with a non-NULL department_id:
SELECT COUNT(department_id)
FROM employees;
Output
| COUNT(department_id) |
|----------------------|
| 4 |
Usage
The COUNT function is commonly used for various purposes, including:
- Obtaining summary statistics about data sets.
- Assessing the size or completeness of result sets.
- Filtering queries based on the existence of non-NULL values in specific columns.
Handling Variations in Syntax
The COUNT function is widely supported across all major SQL database systems and is generally used in the same way. However, some systems may have minor variations in syntax or behavior.
The COUNT function in SQL is a fundamental tool for performing counting operations on rows in a result set. Whether you're analyzing data, generating reports, or assessing data completeness, understanding how to use COUNT effectively is essential for SQL programming and data manipulation tasks.
