Oracle Command Group by having

First, let's look at the table we want to query:

SQL> select * from EMP; empno ename job Mgr hiredate Sal deptno ---------- --------- ----------- --------- ------ 7369 Smith clerk 7902 1980/12/17 800.00 20 7499 Allen salesman 7698 clerk 1600.00 30 7521 ward salesman 7698 1981/2/22 1250.00 30 7566 Jones manager 7839 million 2975.00 20 7654 Martin salesman 7698 1981/9/28 1250.00 30 7698 Blake manager 7839 1981/5/1 2850.00 30 7782 Clark manager 7839 issue 2450.00 10 7788 Scott analyst 7566 1987/4/19 3000.00 20 7839 King President 1981/11/17 5000.00 10 7844 Turner salesman 7698 1981/9/8 1500.00 30 7876 Adams clerk 7788 1987/5/23 1100.00 20 7900 James Clerk 7698 1981/12/3 950.00 30 7902 Ford analyst 7566 1981/12/3 3000.00 20 7934 Miller clerk 7782 1982/1/23 1300.00 10 14 rows selected

We collect statistics on the job and deptno, and filter out the information of Sal <2500.

 
SQL> select job, deptno, AVG (SAL) as avg_sal from EMP group by job, deptno having AVG (SAL) >=2500 order by job ASC; job deptno avg_sal --------- ------ Analyst 20 3000 manager 20 2975 manager 30 2850 President 10 5000
 

We can see that having for group by is similar to where for select.

Having is used to filter out information that does not meet the conditions in group by and only display information that meets the conditions. For example, in the above example, only information about Sal> = 2500 is displayed.

Group execution consumes a lot of CPU resources. Having and group are also an optimization.