Where and having differences in a database

The HAVING clause is similar to where, but there are also differences, which are the statements that set the conditions.
During the query process

WHERE clause Priority > Aggregate Statement (SUM,MIN,MAX,AVG,COUNT) precedence > HAVING clause

Simply put:
WHERE clause:
Select SUM (num) as $ from order where id>10
Only records with IDs greater than 10 are queried for aggregate statements

HAVING clause:
Select ReportsTo as manager, COUNT (*) as reports from employees
GROUP BY ReportsTo have count (*) > 4
Take the Northwind library as an example. Having conditional expressions are shown as aggregate statements. To be sure, the HAVING clause query process execution priority is lower than the aggregate statement.
In other words, it would be wrong to change the having of the above to where. Aggregate statements are used when grouping data is counted.
Use having when judging the grouped data again. Without these relationships there is no use of having. Just use where on the line.
Having is to compensate for the lack of where in the grouping data judgment. Because the where execution priority is faster than the aggregate statement.

Aggregation 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 function on more than one record.
SELECT SUM (population) from TableName

The SUM function here is on the population field of all returned records, and the result is that the query returns only one result, that is, all
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 regions, a set of data that belongs to the same region will only return one row of values.
That is, all fields except region (regions) in the table can only be returned with a value after the SUM, count, and other aggregate function operations.
The HAVING clause allows us to filter the group data after the group.
The HAVING clause filters the group records after aggregation
The WHERE clause filters the records before aggregating. In other words, before the GROUP BY clause and the HAVING clause
Let's look at the following examples:

First, show the total population and area of each area.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
The return record is divided into groups with region first, 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 an aggregate function.

Second, show the total population and area of each area. Only those areas with more than 1000000 area are shown.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
Have SUM (area) >1000000
Here, we cannot use where to filter more than 1000000 of the area, because there is no such record in the table.
Instead, the HAVING clause allows us to filter groups of data after the group.

The following example uses a database that is MySQL 5.
Data sheet: 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/profession/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, F
7 Jak Phy (f)
8 Jak Draw (f)
9 Leo Chinese F

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

Result:
ID name major score sexsum (s.score)
1 Jak Chinese f 248
3 Leo Phy 220 F

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 usethe aggregation function.
Aggregate functions such as count (), sum () can be used only if the group by statement is used.

Let's further filter the above results to show 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 have sum (s.score) >230

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

It can be seen that having the same function in where.

Conclusion:
The 1.WHERE clause is used to filter the rows produced by the operation 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.

Where and having differences in a database