Differences and usage between group by, where, and having

 

Having clauses are similar to where clauses, but they are also different. They are all statements that set conditions.
The aggregate statement (sum, Min, Max, AVG, count) takes precedence over the having clause in the query process. the where clause takes precedence over aggregate statements (sum, Min, Max, AVG, count) in the query process ).
Simply put:
Where clause:
Select sum (Num) as RMB from order where ID> 10
// Only records with IDs greater than 10 can be queried for aggregation.

Having clause:
Select reportsto as manager, count (*) as reports from employees
Group by reportsto having count (*)> 4
Take the northwind database as an example. Having condition expression as an aggregate statement. Certainly, the having clause query takes precedence over aggregation statements.
In other words, changing having to where will result in an error. Aggregate statements are used to count grouped data.
Having is used to deduce group data again. We assume that having is not used without these relationships. Use Where directly.
Having is used to make up for the insufficiency of where when grouping data inference. The where operation takes precedence over aggregation statements.

Aggregate functions:
Such as sum, Count, Max, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records.
Select sum (Population) from tablename

Here sum applies to the population field of all returned records. The result is that only one result is returned for this query, that is, all
Total population of the country. By using the group by clause, the sum and count functions can work on a group of data.
When you specify group by region, only one row of data belonging to the same region can be returned.
That is to say, all fields in the table except region (region) can only return a value after sum, count, and other aggregate function operations.
Having clause allows us to filter data of groups after grouping.
Having clause filters group records after aggregation
The where clause filters records before aggregation, that is, before the Group by clause and having clause
See the following examples:

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. The total population and total area of each region are displayed. Only those regions with an area exceeding 1000000 square meters are displayed.
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 regions, because such a record does not exist in the table.
On the contrary, the having clause allows us to filter the group data.

 

The following example shows that the database used 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:
No./name/major/credits/Gender
ID name Major score sex
1 Jak Chinese 40 F
2 rain math 89 m
3 Leo PHY 78 F
4 Jak math 76 F
5 rain Chinese 56 m
6 Leo math 97 f
7 Jak PHY 45 f
8 Jak draw 87 F
9 Leo Chinese 45 f

Now we want to get a view:
The following table lists the total scores of each student:
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 40 f 248
3 Leo PHY 78 F 220

We can see that there are two groups in total. The two groups of students are Jak and Leo, and each group is the same student, so that we can use the aggregate function.
Only the group by statement can be used to use aggregate functions such as Count () and sum.

We will further filter the above results to show only the students whose total score is greater than 230:
SQL:
Select S. *, sum (S. Score) from student s where sex = 'F' group by S. Name having sum (S. Score)> 230

Result:
ID name Major score sex sum (S. Score)
1 Jak Chinese 40 f 248

It can be seen that having has almost the same functionality as where.

Conclusion:
1. The WHERE clause is used to filter the rows generated by the operation specified in the from clause.
2. The group by clause is used to group the output of the WHERE clause.
3. The having clause is used to filter rows from grouping results.

 

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