Use of having clauses in oracle

First, let's take a look at Aggregate functions, such as SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records, selectcount (unit_id) fromsys_user; here, COUNT serves on the unit_id field of all returned records, the result indicates that only one result is returned, that is, the total number. By using GR

First, let's take a look at Aggregate functions, such as SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they are generally used to select count (unit_id) from sys_user on multiple records; the COUNT here serves on the unit_id field of all returned records, the result indicates that only one result is returned, that is, the total number. By using GR

First, let's take a look at Aggregate functions, 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 count (unit_id) from sys_user; here, COUNT applies to the unit_id field of all returned records. The result is that only one result is returned for this query, that is, the total number.

BY using the group by clause, SUM and COUNT functions can be used for a GROUP of data. When you specify broup by unit_id, a group of data belonging to the same unit_id will be intelligently returned with a row value.

HAVING clause allows us to filter the data of each GROUP after the GROUP. The WHERE clause filters records at the aggregation front, that is, before the group by clause and HAVING clause.

The HAVING clause filters group records after aggregation.

The HAVING clause can filter the results of the summary operation to obtain a dataset with aggregated features meeting a certain condition,

The following shows how to use HAVING through an SQL instance.

Count the number of people under each UNIT_ID, and the number is greater than 50

select unit_id,count(*) from sys_user group by unit_id

In this way, we can find the number of people under each UNIT_ID. To get more than 50 people, we need the HAVING clause.

select unit_id,count(*) from sys_user group by unit_id having count(*) > 50;