Group statistics
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 can be returned, that is, all fields except region (region) in the table, only one value can be returned after sum, count, and other aggregate function operations.
Having clause allows us to filter the data of each group after grouping. The WHERE clause filters records before aggregation. that is to say, the function is prior to the group by clause and having clause, while 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. 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.