Usage of group by, having, and order

1. If a query uses the grouping function, any column or expression that is not in the grouping function must be in group by; otherwise, an error occurs.
 
In the first query, deptno does not appear in group by or in the grouping function. Therefore, an error occurs. Change deptno to job. The meaning of this statement is: Find the average salary of each job by group, having clause

Oracle requires that the where clause cannot use the grouping function. In this case, we must use the having clause to complete the function.

Select job, avg (sal) from emp having avg (sal)> 1500 group by job;

When the having clause is used, the oracle system processing sequence is:

1. Group data rows first.

2. Apply the group to the grouping function.

3. It is best to display records that meet the having condition.

3. Nesting of grouping Functions

Not to mention direct
 
The statement execution sequence is:

1. Execute the where clause to find the job's qualified records.

2. Group jobs.

3. Calculate the average salary of each group based on the grouping conditions.

4. Find the largest and smallest average salaries in each group.