How can we apply filtering criteria at group levels of the result set returned by MySQL?

As we know that GROUP BY clause in a SELECT statement can divide the result set, returned by MySQL, in groups. Now if we want to return only some specific groups then need to apply filtering criteria at the group level. It can be done by using HAVING clause inside the GROUP BY clause. The example below will demonstrate it −

Example

Suppose we want to return only the group which is having an average salary of 55000 then we need to use filtering criteria as follows in HAVING clause −

mysql> Select count(*),AVG(salary),Designation from employees GROUP BY designation having AVG(salary) = 55000;

+----------+-------------+-------------+
| count(*) | AVG(salary) | Designation |
+----------+-------------+-------------+
| 2        | 55000.0000  | Asst.Prof   |
+----------+-------------+-------------+

1 row in set (0.00 sec)