Introduction to Oracle Grouping functions __ static functions

Grouping functions

By learning the group function today, we have the following learning content.

1, what is the grouping function.

A: A grouping function acts on a set of data and returns a value for a set of data.

2, the common grouping functions are:

Function name	Function description
Count	Returns the number of records found
Min	Returns the minimum value of a numeric column or computed column
Max	Returns the maximum value of a numeric column or computed column
Sum	Returns a numeric column or computed column sum
Avg	Returns the average of a numeric column or computed column

3, AVG (average) and SUM (total) functions

Case:sql> Select AVG (SAL), sum (SAL) from EMP;

4, min (minimum) and MAX (max) function

Case:sql> Select min (sal), Max (SAL) from EMP;

5, count (count) function

Case:sql> Select COUNT (ename) from EMP;

6, distenct keyword: remove duplicate.

7. Grouped data: GROUP BY clause syntax

The GROUP BY statement is understood in the literal sense of the English language as grouping (group) according to certain rules. Its role is to divide a dataset into small areas through certain rules, and then data processing for several small regions. Use the GROUP BY clause when you want to group the values of a column to count the information for the data in that group during the query. The GROUP BY clause can be used regardless of whether the select uses a WHERE clause.

Note: The GROUP BY clause must be used in conjunction with the grouping function, otherwise it is meaningless.

Case:

Find out the number of employees in each department

Sql> Select Deptno,count (*) as "number" from the EMP Group by DEPTNO;

Find out the average wage of employees in each department

Sql> Select Deptno,avg (NVL (sal,0)) from the EMP group by DEPTNO;

Note: The columns in the GROUP BY clause do not have to be included in the select list

Sql> Select AVG (NVL (sal,0)) from the EMP group by DEPTNO;

Find the number of employees in the same position in a department group by fields that can be grouped with multiple groups by

Sql> Select Deptno,job,count (*) from the EMP group by the Deptno,job order by Deptno;

8. Illegal use of group functions

(1) You cannot use group functions in the WHERE clause (note.) ）

Sql> Select Deptno from emp where count (Job) >0 GROUP by Deptno;

Note: ORA-00934: Grouping functions are not allowed here

(2) You can use group functions in the HAVING clause

9. Filtration Grouping: HAVING clause

Group with having filter:

(1) row has been grouped

(2) Use of Group functions

(3) The grouping that satisfies the conditions in the HAVING clause will be displayed

The AVING clause sets the condition to the GROUP by clause in a way that interacts with the WHERE clause and the SELECT statement. The WHERE clause search condition is applied before the group operation, and the having search condition is applied after the group operation. The having syntax is similar to the WHERE syntax, but the having can contain aggregate functions. A HAVING clause can refer to any item that appears in the select list.

NOTE: The HAVING clause is usually used in conjunction with the GROUP BY clause

Case:

The number of employees in the enquiry department is greater than five department numbers.

Sql> Select Deptno,count (*) from EMP GROUP BY DEPTNO has count (*) >5;

10. Nested functions

(1) functions can be nested. The output of one function can be the input of another function. The operand has an inheritable execution process. But the precedence of the function is based only on the position, and the function follows the principle from the inside out to the left to the right.

(2) Nested functions can include nested single-line functions in a group function, or a group function nested within a single line function or group function