MySQL count() Function

Last Updated: 26 July, 2023

In MySQL, the COUNT() function is an aggregate function; it is used to return the number of rows that match a specific condition or criteria within a table. It is commonly used in conjunction with the SELECT statement.

It can be used in various ways depending on the requirements; here are a few examples of how the COUNT() function can be used:

Suppose we have a table called employees with the following structure:

Table: employees

  +----+-----------+------------+--------+------------+---------------+
  | ID | Name      | Department |   Age  |   Salary   |  JoiningDate  | 
  +----+-----------+------------+--------+------------+---------------+
  | 1  | Rajesh    | Sales      |   43   |    54,000  |   2023-06-11  |
  | 2  | Kamal     | HR         |   35   |    38,000  |   2021-01-23  |
  | 3  | Vimal     | Sales      |   52   |    68,000  |   2022-04-08  |
  | 4  | Hina      | Marketing  |   38   |    72,000  |   2018-08-16  |
  | 5  | Jyanti    | Finance    |   29   |    60,000  |   2010-02-24  |
  | 6  | Sanjay    | HR         |   31   |    55,000  |   2023-01-29  |
  | 7  | Amar      | Finance    |   27   |    40,000  |   2023-07-19  | 
  +----+-----------+------------+--------+------------+---------------+

Example 1: Counting all rows in a table

Counting the total number of records in a table using the count(*) function.

Syntax:

  SELECT COUNT(*) FROM table_name;

Example:

  mysql> SELECT COUNT(*) FROM employees;
  +----------+
  | COUNT(*) |
  +----------+
  |        7 |
  +----------+
  1 row in set (0.04 sec)

Here, we are counting the total number of rows in an employees table.

Example 2: Counting rows based on the condition

We can count specific types of records using the WHERE clause of a table.

Syntax:

  SELECT COUNT(*) FROM table_name WHERE condition;

Example:

  mysql> SELECT COUNT(*) FROM employees WHERE age > 30;
  +----------+
  | COUNT(*) |
  +----------+
  |        5 |
  +----------+
  1 row in set (0.05 sec)

Here, we are counting the total number of records for an employee whose age is greater than 30.

Example 3: Counting distinct values in a column

It returns the number of distinct rows where the expression evaluates to a non-NULL value.

Syntax:

 SELECT COUNT(DISTINCT column_name) FROM table_name;

Example:

 mysql> SELECT COUNT(DISTINCT Department) FROM employees;
 +----------+
 | COUNT(*) |
 +----------+
 |        4 |
 +----------+
 1 row in set (0.03 sec)

Here, we are counting the total number of unique departments in an employees table.

The COUNT() function returns a single result, which is the number of rows that match the specified condition. We can use it in conjunction with other SQL clauses, such as WHERE, GROUP BY, HAVING, etc., to further refine your queries.

Here are some additional notes about the COUNT() function:

• The COUNT() function returns a BIGINT value.
• The COUNT() function ignores NULL values.
• The ORDER BY clause is not a factor in the COUNT() function.