The groupby and having clauses in the SELECT command

Before introducing the group by and having clauses, we must first talk about a special function in SQL: aggregate function, 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 BBC
Sum is used in the population field of all returned records. The result is that only one result is returned for this query, that is, the total population of all countries.

By using the group by clause, sum and count functions can be used for a group of data. When you specify group by region, only one row of data belonging to the same region is returned. that is to say, all fields except region (region) in the table can only return a value after sum, count, and other aggregate function operations.

Having clause allows us to filter data of groups after grouping. the where clause filters records before aggregation. that is to say, it takes effect before the Group by clause and having clause. the having clause filters group records after aggregation.

Let's still understand the group by and having clauses through specific instances, and use the BBC table introduced in section 3.

SQL instance:
1. shows 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.
ArticleSource: http://www.diybl.com/course/comshiti/mirosoft/mfd/mcdba/2007522/37086.html