How to Use the COUNT() Function in MySQL

08/04/2021

Contents

In this article, you will learn how to use the COUNT() function in MySQL.

Using the COUNT() function in MySQL

The COUNT() function in MySQL is used to count the number of rows that match a specific criterion in a table.

Syntax

Here’s the basic syntax of the COUNT() function in MySQL:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

In this example, “column_name” is the name of the column we want to count, “table_name” is the name of the table, and “condition” is the criterion that must be met in order for a row to be counted. If we want to count all rows in the table, we can simply omit the “WHERE” clause.

Using COUNT() with DISTINCT

We can also use the COUNT() function with the DISTINCT keyword to count the number of unique values in a column. Here’s the syntax:

SELECT COUNT(DISTINCT column_name)
FROM table_name
WHERE condition;

In this example, the COUNT() function will count the number of distinct values in the specified column.

Example of using COUNT()

Let’s look at an example to see how we can use the COUNT() function in MySQL. Suppose we have a table called “employees” with the following data:

+----+-----------+------------+
| id | name      | department |
+----+-----------+------------+
| 1  | John Doe  | IT         |
| 2  | Jane Smith| HR         |
| 3  | Bob Brown | IT         |
| 4  | Sarah Lee | IT         |
| 5  | Tom Chen  | HR         |
+----+-----------+------------+

We can use the COUNT() function to count the number of rows in the “employees” table as follows:

SELECT COUNT(*)
FROM employees;

The result will be:

+----------+
| COUNT(*) |
+----------+
| 5        |
+----------+

We can also use the COUNT() function to count the number of rows that meet a specific condition. For example, if we want to count the number of employees in the “IT” department, we can use the following query:

SELECT COUNT(*)
FROM employees
WHERE department = 'IT';

The result will be:

+----------+
| COUNT(*) |
+----------+
| 3        |
+----------+

We can use the COUNT() function with the DISTINCT keyword to count the number of unique departments in the “employees” table as follows:

SELECT COUNT(DISTINCT department)
FROM employees;

The result will be:

+----------------------+
| COUNT(DISTINCT depatment) |
+----------------------+
| 2                    |
+----------------------+

In this example, the COUNT() function will count the number of unique values in the “department” column, which are “IT” and “HR”.

#MySQL