MySQL COUNT Function

The COUNT function in MySQL returns the total count of rows returned by the SELECT statement.

You may use it with the simple SELECT statement to get the count of all rows as a single number.

The COUNT function can also be used with the GROUP BY clause to get the count of rows for each group separately.

The syntax of using the COUNT function

This is how the MySQL COUNT function is used:

The example of using COUNT function

For the examples, I am using the demo table (tst_employees) that stores employee records. In this query, we will get the total count in the table i.e. COUNT(*):

Example short description

Query:

The result:

From the graphic above, it can be seen that the first query returned complete table data.

The table contains seven records of employees so the next query that used COUNT(*) also returned 7.

Using WHERE condition with COUNT function

This query returns the count of those employees whose salary is greater than or equal to 4000.

The Query:

SELECT COUNT(*) AS "Employee >=4000" FROM tst_employees

WHERE employee_salary >= 4000

The result:

Again, the first query shows all table records while the second query uses the COUNT function.

You may compare the result returned by COUNT and complete table data that only five employees’ salaries are greater than or equal to 4000.

Using COUNT function with GROUP BY example

As mentioned earlier, to get the count of rows separated by groups, you may use the COUNT function with GROUP BY.

In the following example, I am using another table that stores employees’ salaries paid every month.

The table name is tst_salary_paid and we will get the number of salaries paid to each employee stored in the table:

The COUNT/GROUP BY query:

SELECT employee_name AS  "Name of Employee", COUNT( employee_salary_paid ) AS  "Total Salaries Paid"

FROM tst_emp_paid_sal

GROUP BY employee_name

The result-set:

Above, you can see all table rows and then COUNT with GROUP BY clause returned the number of salaries paid to each employee.

An example of using ORDER BY clause with COUNT

I just added the ORDER BY clause in the above query to sort the results by the maximum number of salaries to the minimum:

The ORDER BY/COUNT query:

SELECT employee_name AS  "Name of Employee", COUNT( employee_salary_paid ) AS  "Total Salaries Paid"

FROM tst_emp_paid_sal

GROUP BY employee_name

ORDER BY  "Total Salaries Paid"

The result:

If you compare it with the above result-set, you may notice the order of employees is based on the highest to lowest number of salaries.