How to filter group records using having clauses in MySql _ MySQL

How to filter group records using having clauses in MySql bitsCN.com Having usage
Having clauses allow us to filter various data after grouping. where clauses filter records before aggregation, that is, before group by and having clauses. The having clause filters group records after aggregation.

SQL instance:
1. display the total population and total area of each region
SELECT region, SUM (population), SUM (area) FROM bbc group by region
First, return records are divided into multiple groups BY region, which is the literal meaning of group. After grouping, aggregate functions are used to calculate different fields (one or more records) in each group.

2. show the total population and total area of each region. only show the regions with an area of more than 1000000 square meters
SELECT region, SUM (population), SUM (area) FROM bbcGROUP BY regionHAVING SUM (area)> 1000000
Here, we cannot use where to filter more than 1000000 of the regions, because such a record does not exist in the table. On the contrary, the having clause allows us to filter the data of each group after grouping.

Mysql determines the length of a field
Select home_page from aaa table where char_length (trim (home_page) 1;

Differences between the where and having clauses in mysql
The where and having clauses in mysql can both implement the filter record function, but their usage is still different. for example:
Use the group by clause and having clause to detect records that are not repeated. the SQL statement is as follows:
Select uid, email, count (*) as ct from 'edm _ user081217 'group by email
Then you can easily understand this.
Select uid, email, count (*) as ct from 'edm _ user081217 'group by email HAVING ct> 1
Use group by to group emails, and use having to filter records greater than 1. in this way, duplicate records are found.

Differences between having and where:
Select city FROM weather WHERE temp_lo = (SELECT max (temp_lo) FROM weather );
Different objects. The WHERE clause acts on tables and views, and the HAVING clause acts on groups. WHERE selects the input row before grouping and clustering calculation (therefore, it controls which rows enter clustering calculation), while HAVING selects the row of the group after grouping and clustering. Therefore, the WHERE clause cannot contain clustering functions, because it is meaningless to try to use clustering functions to judge the rows input to clustering operations. Conversely, HAVING clauses always contain aggregate functions. (Strictly speaking, you can write a HAVING clause that does not use clustering, but it is only effort-consuming. The same condition can be used more effectively in the WHERE stage .) In the previous example, we can apply the city name restriction in the WHERE clause because it does not need to be clustered. This is more efficient than adding restrictions to HAVING, because we avoid grouping and clustering calculation for rows that do not pass the WHERE check. In summary, having is generally followed by group, execute part of the record group to work. Where is used to execute all data. In addition, having can use aggregate functions, such as having sum (qty)> 1000bitsCN.com.