MySQL 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:
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:
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:
Output:
Example3
This statement uses the COUNT(distinct expression) function that counts the Non-Null and distinct rows in the column emp_age:
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:
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:
This statement will give the output as below:
