Use the having keyword in Oracle to limit your grouping results

We have introduced how to useOracleAggregate functions andGroupKeyword to group the result set. A new problem arises. For example, we need to find the grouping results that meet the conditions for the column of the aggregate function. We can useWhereClause? No, we need to useHavingKeyword.

For example, we need to find out$2000The above departments. UseHavingKeyword Syntax:

Select deptno, AVG (SAL) avg_sal

From EMP

Group by deptno

Having AVG (SAL) & gt; 2000;

In this way, the correct result can be obtained.

If you are using

Select deptno, AVG (SAL) avg_sal

From EMP

Group by deptno

Where AVG (SAL)> 2000;

Will appearORA-00934.

AboutOracleOfHavingThe keyword cannot be inHavingAlias for Using Aggregate functions,For example

Select deptno, AVG (SAL) avg_sal

From EMP

Group by deptno

Having avg_sal & gt; 2000;

:ORA-00904: 'avg_sal ":Invalid identifier error.

Of courseHavingKeywords can only be used to process the results of Aggregate functions, and cannot be used in common columns.

The result of the preceding example:

SQL> select deptno, AVG (SAL) avg_sal

2 from EMP

3 group by deptno

4 having AVG (SAL)> 2000;

 

Deptno avg_sal

--------

10 2916.66667

20 2235