The difference and usage between GROUP by,where,having

The HAVING clause is similar to where it is, but there is also a difference, which is the statement that sets the condition.
Aggregate statements (Sum,min,max,avg,count) are executed more than the HAVING clause in the query process. The WHERE clause takes precedence over the aggregate statement (SUM,MIN,MAX,AVG,COUNT) in the query process.
In short,
WHERE clause:
Select SUM (num) as RMB from the order where id>10
You can aggregate statements only if you first query for records with IDs greater than 10

HAVING clause:
Select ReportsTo as manager, COUNT (*) as reports from employees
GROUP BY ReportsTo has count (*) > 4
Take the Northwind library as an example. The having condition expression is an aggregation statement. Definitely say HAVING clause query process execution priority is lower than aggregate statement.
In other words, it would be an error to replace the previous having with a where. Aggregate statements are used to count grouped data.
The having is used to judge the grouped data again. Without these relationships there is no use of having. Just use the Where.
Having is to make up for where the problem is when the grouped data is judged. Because the where execution priority is faster than the aggregate statement.

Aggregate function, which is a special function that must be spoken first:
such as Sum, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally work on more than one record.
SELECT SUM (population) from TableName

The sum action here is on all population fields that return records, and the result is that the query returns only one result, that is, all
The total population of the country. By using the GROUP BY clause, you can have the sum and COUNT functions work on data that belongs to a group.
When you specify group by region, a set of data belonging to the same region (region) will only return one row of values.
That is, all fields except the region (region) in the table can only return a value after the aggregate function operation SUM, count, and so on.
The HAVING clause allows us to filter the groups of data after the group.
The HAVING clause filters the group records after aggregation
The WHERE clause filters the records before aggregating. That is, the effect is before the GROUP BY clause and the HAVING clause
Look at the following examples:

Show the total population number and area of each area.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
First, you divide the return records into groups by region, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with aggregate functions.

Show the total population number and area of each area. Show only those areas with an area of more than 1000000.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
Having SUM (area) >1000000
Here, we cannot use where to filter more than 1000000 of the region because no such record exists in the table.
Instead, the HAVING clause allows us to filter the groups of data after the group.

The following example uses a database that is MySQL 5.
Data table: Student
Table structure:
Field Name DataType Len
ID int 20
Name varchar 25
Major varchar 25
Score int 20
Sex varchar 20

Table data:
Number/name/major/credits/gender
ID name major score sex
1 Jak Chinese F
2 Rain Math m
3 Leo Phy F
4 Jak Math F
5 Rain Chinese m
6 Leo Math the F
7 Jak Phy F
8 Jak Draw F
9 Leo Chinese F

Now we're going to get a view:
Ask for sex as a boy, and list each student's total score:
Sql:
Select S.*,sum (s.score) from student s where sex= ' F ' GROUP by S.name

Result:
ID name major score sex sum (S.score)
1 Jak Chinese F 248
3 Leo Phy F 220

You can see that there are two groups, two groups of students are Jak and Leo, each group is the same student, so we can use the aggregate function.
Aggregate functions such as count (), sum () can be used only if the group by statement is used.

Let's do a further screening of the results above, showing only students with a total score of more than 230:
Sql:
Select S.*,sum (s.score) from student s where sex= ' F ' GROUP by S.name has sum (s.score) >230

Result:
ID name major score sex sum (S.score)
1 Jak Chinese F 248

You can see the function of having a where is similar.

Conclusion:
The 1.WHERE clause is used to filter the rows generated by the actions specified in the FROM clause.
The 2.GROUP by clause is used to group the output of the WHERE clause.
The 3.HAVING clause is used to filter rows from the grouped results.

Excerpt from: http://hi.baidu.com/somchai/blog/item/23a2602568abf439c895594c.html