Apply Filtering Criteria at Group Level in MySQL

Q

How To Apply Filtering Criteria at Group Level in MySQL?

✍: FYIcenter.com

A

If you want to return only specific groups from the query, you can apply filtering criteria at the group level by using the HAVING clause inside the GROUP BY clause. Note group functions can also be used in HAVING conditions. The following tutorial exercise gives you some good HAVING example:

mysql> SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links GROUP BY tag HAVING AVG(counts) > 3.8;
+------+----------+---------------------+-------------+
| tag  | COUNT(*) | MIN(created)        | AVG(counts) |
+------+----------+---------------------+-------------+
| DEV  |        2 | 2004-01-01 00:00:00 |      4.0000 |
| SQA  |        2 | 2003-01-01 00:00:00 |      7.0000 |
+------+----------+---------------------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links GROUP BY tag HAVING COUNT(*) > 2;
+------+----------+---------------------+-------------+
| tag  | COUNT(*) | MIN(created)        | AVG(counts) |
+------+----------+---------------------+-------------+
| DBA  |        3 | 2005-01-01 00:00:00 |      3.6667 |
+------+----------+---------------------+-------------+
1 row in set (0.00 sec)

 

SELECT Query Statements with GROUP BY in MySQL

⇒⇒MySQL Database Tutorials

2017-10-16, 280👍, 0💬