Oracle's GROUP BY and having-----turned

Before we introduce the group BY and HAVING clause, we must first talk about a special function in the SQL language: aggregate functions 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 BBC
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, the total population of all countries.
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 return only 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.
Having clauses allows us to filter groups of data, where clauses filter records before they are aggregated. That is, before the GROUP BY clause and the HAVING clause.
The HAVING clause filters the group records after aggregation.
Let's take a concrete example to understand the group BY and having clauses, as well as the BBC table introduced in section Iii.
SQL instance:
First, show the total population and area of each region:
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 displayed.
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.

Category: Oracle

Oracle's GROUP BY and having-----turned