Count() Function

MySQL count() function is used to returns the count of an expression. It allows us to count all rows or only some rows of the table that matches a specified condition. It is a type of aggregate function whose return type is BIGINT. This function returns 0 if it does not find any matching rows.

We can use the count function in three forms, which are explained below:

• Count (*)
• Count (expression)
• Count (distinct)

Let us discuss each in detail.

COUNT(*) Function: This function uses the SELECT statement to returns the count of rows in a result set. The result set contains all Non-Null, Null, and duplicates rows.

COUNT(expression) Function: This function returns the result set without containing Null rows as the result of an expression.

COUNT(distinct expression) Function: This function returns the count of distinct rows without containing NULL values as the result of the expression.

Syntax

The following are the syntax of the COUNT() function:

   SELECT COUNT (aggregate_expression)    

FROM table_name    

[WHERE conditions];   

Parameter explanation

aggregate_expression: It specifies the column or expression whose NON-NULL values will be counted.

table_name: It specifies the tables from where you want to retrieve records. There must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.

MySQL count() function example

Consider a table named “employees” that contains the following data.

Let us understand how count() functions work in MySQL.

Example1

Execute the following query that uses the COUNT(expression) function to calculates the total number of employees name available in the table:

mysql> SELECT COUNT(emp_name) FROM employees;    

Output:

Example2

Execute the following statement that returns all rows from the employee table and WHERE clause specifies the rows whose value in the column emp_age is greater than 32:

mysql> SELECT COUNT(*) FROM employees WHERE emp_age>32;    

Output:

Example3

This statement uses the COUNT(distinct expression) function that counts the Non-Null and distinct rows in the column emp_age:

mysql> SELECT COUNT(DISTINCT emp_age) FROM employees;  

Output:

MySQL Count() Function with GROUP BY Clause

We can also use the count() function with the GROUP BY clause that returns the count of the element in each group. For example, the following statement returns the number of employee in each city:

mysql> SELECT emp_name, city, COUNT(*) FROM employees GROUP BY city;  

After the successful execution, we will get the result as below:

MySQL Count() Function with HAVING and ORDER BY Clause

Let us see another clause that uses ORDER BY and Having clause with the count() function. Execute the following statement that gives the employee name who has at least two age same and sorts them based on the count result:

mysql> SELECT emp_name, emp_age, COUNT(*) FROM employees   

GROUP BY emp_age   

HAVING COUNT(*)>=2   

ORDER BY COUNT(*);  

This statement will give the output as below: