Oracle grouping query, oracle grouping

Oracle grouping query, oracle grouping

Grouping Functions
In the grouping function, if a search item group exists, other items must be grouped. For example, the following statement reports an error because sal is grouped, but ename is not grouped:

1. display the employees with the highest salary:

2. display the average salary of all employees:
2.1 use system functions:

2.2 custom computing:

3. display the information of employees whose salaries are higher than the average salary:

Grouping statistics on query results:

4. query the highest and average salaries of each department:

5. display the average and maximum salaries for each position in each department:

6. Show the Department number with an average salary lower than 2000 and its average salary:

Summary of data groups

1 The grouping function can only appear in the selection list, having, and order by clauses (not in the where clause)
2 If select statements contain group by, having, and order by statements, the order is group by, having, and order.
3. If there are columns, expressions, and grouping functions in the selection column, one of these columns and expressions must appear in the group by clause; otherwise, an error occurs.
For example, SELECT deptno, AVG (sal), MAX (sal) FROM emp GROUP by deptno having avg (sal) <2000;
Here, deptno must appear in group.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.