SQL COUNT() Function
The SQL COUNT() function returns the number of rows that matches a specified criterion. It is a powerful tool for aggregating data in SQL queries.
Basic Usage
The COUNT() function can be used to count the number of rows in a table.
SELECT COUNT(*) AS total_rows FROM employees;This query counts the total number of rows in the employees table.
Counting Non-NULL Values
The COUNT() function can also be used to count the number of non-NULL values in a specific column.
SELECT COUNT(salary) AS total_salaries FROM employees;This query counts the number of non-NULL salaries in the employees table.
Using COUNT() with DISTINCT
The COUNT(DISTINCT column_name) function can be used to count the number of distinct (unique) values in a column.
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;This query counts the number of unique departments in the employees table.
Using COUNT() with GROUP BY
The COUNT() function is often used with the GROUP BY clause to count the number of rows for each group.
SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department;This query counts the number of employees in each department.
Example 1: Counting Rows with a WHERE Clause
SELECT COUNT(*) AS number_of_sales_employees
FROM employees
WHERE department = 'Sales';This query counts the number of employees in the Sales department.
Example 2: Counting Non-NULL Values with a WHERE Clause
SELECT COUNT(salary) AS number_of_salaries
FROM employees
WHERE department = 'Marketing';This query counts the number of non-NULL salaries in the Marketing department.
Example 3: Counting Distinct Values
SELECT COUNT(DISTINCT job_title) AS unique_job_titles
FROM employees;This query counts the number of unique job titles in the employees table.
Example 4: Using COUNT() with GROUP BY and HAVING
SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;This query counts the number of employees in each department and only returns departments with more than 10 employees.
The COUNT() function is a versatile tool for aggregating data in SQL. It can be used to count rows, non-NULL values, distinct values, and more, often in combination with GROUP BY and HAVING clauses.