What is 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:

SELECT COUNT(col_name) FROM tbl_name

WHERE condition

GROUP BY col1,….

ORDER BY col1,…

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:

SELECT COUNT(*) FROM tst_employees

The result:

MySQL COUNT

From the graphic above, it can be seen that the first query returned complete table data. The table contains seven records of employees and 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:

MySQL COUNT WHERE

Again, the first query shows all table records while the second query used COUNT MySQL function. You may compare the result returned by COUNT and complete table data that only five employees’ salary is greater than or equal to 4000.

Using COUNT function with GROUP BY example

As mentioned earlier, for getting 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:

MySQL COUNT group

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 ORDER BY clause in 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:

COUNT order by

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

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!