MySQL COUNT() Function

MySQL COUNT function returns the number of records in a select query and allows you to count all rows in a table or rows that match a particular condition.

MySQL COUNT function Syntax

The syntax for the COUNT function in MySQL is:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];

To understand MySQL COUNT function, consider an employees table, which is having the following records −

SELECT * FROM employees;

Now suppose based on the above employees table you want to count total number of records in this table, then you can do it as follows −

SELECT COUNT(*) AS "Total Rows" FROM employees;

Similarly, if you want to count the number of records for technology department, then it can be done as follows −

SELECT COUNT(*) AS "Total technology department" 
FROM employees 
WHERE department = "Technology";

Example – Using DISTINCT

You can use the DISTINCT clause within the COUNT function. For example, the MySQL statement below returns the number of unique department where at least one employee makes over 7000 salary.

SELECT COUNT(DISTINCT department) AS "Unique departments"
FROM employees
WHERE salary > 7000;

Example – Using GROUP BY

In some cases, you will be required to use the GROUP BY clause with the COUNT function.

For example, you could also use the COUNT function to return the name of the department and the number of employees in the associated department.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
GROUP BY department;