MySQL COUNT() Function

Introduction

In this chapter, we will learn about the COUNT() function in MySQL. The COUNT() function is an aggregate function that returns the number of rows that match a specified condition. It is widely used to count the total number of rows in a table, count rows that meet specific criteria, and group and count rows based on certain attributes. We will cover the syntax for the COUNT() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the COUNT() function in MySQL is:

SELECT COUNT(column_name)
FROM table_name
[WHERE condition];

Variants

• COUNT(*): Counts all rows in the table, including rows with NULL values.
• COUNT(column_name): Counts non-NULL values in the specified column.
• COUNT(DISTINCT column_name): Counts unique non-NULL values in the specified column.

Examples

Let’s go through examples for each variant of the COUNT() function using the employees table.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Rahul', 'Sharma', 'Sales', 50000.00),
('Priya', 'Singh', 'Marketing', 60000.00),
('Amit', 'Kumar', 'Sales', 55000.00),
('Neha', 'Verma', 'IT', 58000.00),
('Sahil', 'Mehta', 'IT', 62000.00),
('Kiran', 'Patel', 'Sales', 53000.00);

2. COUNT(*)

Example: Count All Employees

SELECT COUNT(*) AS employee_count FROM employees;

Output

3. COUNT(column_name)

Example: Count Non-NULL Salaries

SELECT COUNT(salary) AS non_null_salaries FROM employees;

Output

4. COUNT(DISTINCT column_name)

Example: Count Distinct Departments

SELECT COUNT(DISTINCT department) AS distinct_departments FROM employees;

Output

Using COUNT() with WHERE Clause

The COUNT() function can be used with the WHERE clause to count rows that meet specific criteria.

Example: Count Employees in the Sales Department

SELECT COUNT(*) AS sales_employee_count FROM employees WHERE department = 'Sales';

Output

Using COUNT() with GROUP BY Clause

The COUNT() function is often used with the GROUP BY clause to group and count rows based on certain attributes.

Example: Count Employees by Department

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Output

Important Considerations

• Performance: The COUNT() function can impact performance, especially on large datasets. Consider indexing columns used in the WHERE clause to improve performance.
• NULL Values: COUNT(*) counts all rows, including those with NULL values, while COUNT(column_name) counts only non-NULL values.
• Distinct Count: Use COUNT(DISTINCT column_name) to count unique values in a column, but be aware that this can be more computationally intensive.

Conclusion

The COUNT() function in MySQL is used for counting rows that match specified conditions. This chapter covered the syntax for using the COUNT() function, provided examples of its usage, and discussed important considerations.