Simple SQL Tutorial GROUP by and having

Before introducing the group BY and having clauses, 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 work on more than one record.

SELECT SUM (population) from BBC

The sum action here is on all population fields that return records, and the result is that the query returns only one result, 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 region, a set of data that belongs to the same region (region) will only return one row of values, that is, all fields except region (region) in the table can only return a value after the aggregate functions such as SUM, count, and so on.

The HAVING clause allows us to filter the groups of data after which the WHERE clause filters the records before aggregation. That is, the effect 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:

Show the total population and area of each area:

The following are the referenced contents: SELECT region, sum (population), sum (area) From BBC GROUP by region

First, you divide the return records into groups by region, 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 aggregate functions.

Show the total population number and area of each area. Show only those areas with an area of more than 1000000.

The following are the referenced contents: 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 region because no such record exists in the table.

Instead, the HAVING clause allows us to filter the groups of data after the group.