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
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
SELECT COUNT(*) FROM tst_employees
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.
SELECT COUNT(*) AS "Employee >=4000" FROM tst_employees WHERE employee_salary >= 4000
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
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"
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.