Usage of mysqlhaving and groupby statements

This article uses two simple examples to add restrictions to the group after groupby and query the number of repeated occurrences of a field in the data table. The havaing and groupby statements are sorted by the number of times.

This article uses two simple examples to add restrictions to the group after group by and query the number of repeated occurrences of a field in the data table. The havaing and group by statements are sorted by number of times.

You can use the aggregate function count (*) in Mysql to implement this function. For example, to query the records with the most frequently displayed names in the data table, you can group the records by group by name first, use count to calculate the number of entries in the group, and then sort by count:

The Code is as follows:
Name, count (*) from data group by name order by count (*) DESC limit 1

Data sorted by name will be returned without the limit of limit

Before using the aggregate function, we can use the where clause to add restrictions to the query. What if we need to add restrictions to the data in the group after group? The answer is having.

HAVING clause allows us to filter data of groups after grouping.
The WHERE clause filters records before aggregation, that is, before the group by clause and HAVING clause.
The HAVING clause filters group records after aggregation.

The having clause must contain data after group by and after order.

For example, after sorting names by appearance times in the previous article, we want to find only the data containing lin in the name, which can be written as follows:

The Code is as follows:
Select name, count (*) from data group by name having name like '% lin %' order by couny (*) DESC