oracle database: having filter group information

When obtaining group information, if certain conditions need to be met, you can use having to filter group conditions.
structure:

select ... from ... where ... group by ...having...

Take the following table as an example:

Insert picture description here


gender is still used as the grouping standard here, but a condition must be added, and only those with an average score greater than 90 will be displayed [this is to add a condition to the grouping result].

select sex,avg(english) as english from student group by sex having avg(english)>=90;

Let's take a look at the result:

Insert picture description here


Because the average score of boys is less than 90, it is filtered out.

Syntax execution order:

  1. from student;First know which table to check the content from
  2. group by ... In what group, find the criteria for grouping
  3. having ... What conditions are used to filter the grouped content
  4. select ... What to take from eligible groups

Filter row records and group information

The filtering of row records is filtering for each record, and the filtering of group information is filtering for groups, which can appear at the same time. The row information is filtered first, and then the group information is filtered.

  1. where Filter rows, only row information can appear
  2. having Filter group, only group information can appear

Structure: select ... from ... where ... group by ... having ...
Order of execution:

  1. from ...
  2. where ...
  3. group by ...
  4. having ...
  5. select ...

Here we are still studentexperimenting with the previous table, but here we add a few pieces of data to it for easy operation.

insert into student values(7,'小红','女',19,86,70);
insert into student values(8,'小兰','女',18,77,66);
insert into student values(9,'小艳','女',17,69,58);
insert into student values(10,'热巴','女',22,83,84);
insert into student values(11,'赵丽颖','女',21,99,98);
insert into student values(12,'鞠婧祎','女',24,62,76);

After adding, view all the data in the table as shown below:

Insert picture description here


Here is an example:

  • Query the age range of students and the number of students in that age range
  • Record condition: Only count the students whose math scores are greater than 60 points [ where]
  • Grouping conditions: Only record groups with 2 or more students in the age range【having

Take a look at the specific implementation:

select age,count(age) from student where math>=60 group by age having count(age)>=2;

Execute it to see the result:

Insert picture description here


only 4 students who are 18 years old are eligible.