"Mysql" uses group by companion having to cluster query

Cluster query is the object of the table is one of the columns, such as the following TestTable table, to find out the username column, the number of occurrences of each item, then use the cluster query

Obviously, after the cluster query, the results must be the same as the id,number of the two columns have no relationship. So, just like the door, the object that the cluster query targets is one of the columns of the table.

Cluster queries are often combined with the count (*), SUM (*), AVG (*), and other clustering statements used to count tables. Because the field of the query is often a string varchar type, the COUNT (*) of the number of the string that counts is the most frequently occurring function.

For example, to query the above testtable table, to find out the username column, the number of occurrences of each item, its SQL statement is as follows:

SELECT Username,count (*) as mount from TestTable Group by username ORDER BY Mount Desc

The query results are as follows:

Where order BY is the result of the query in descending order, this is nothing to say.

Comparing the results of the query, you can see that group by means that the same item is gathered together to become a row. The Count function is used to count the number of occurrences and become a single column.

So what if I don't want to show or say I don't want to count less than 1 of the results? At this point you should have a having statement appended to the group by.

For example, the following statement, query username this line occurrences more than 2 items:

SELECT Username,count (*) as mount from TestTable GROUP by username have mount>1 ORDER by mount Desc

Having a group by is not a side-by-side relationship, and having a group by is a subordinate statement that must be preceded by a group by.

Cluster queries with GROUP by companion have often come with "Mysql" using internal joins and nested queries to implement multi-table queries, primary key, foreign key concept (click Open link) multi-table query, find some more depth results.

"Mysql" uses group by companion having to cluster query