Oracle Summary of four ——— grouping functions ____ static functions

Introduction to Grouping functions

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

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

Syntax for grouping functions:

SELECT [Column,] group_function (column), ...

From table

[WHERE condition]

[GROUP by column]

[Order by column];

Returns the total number of records//* represents: a record

Sql> Select COUNT (*) from EMP;

Returns the total number of records that Comm is not empty

Sql> Select COUNT (comm) from EMP;

Count (DISTINCT expr) returns the total number of non-empty and distinct records of expr

Sql> Select COUNT (Distinct (SAL)) from EMP;

Note: The group function ignores null values.

Returns the average wage of all employees

Sql> Select AVG (NVL (sal,0)) from EMP;

Note: The NVL function makes it impossible for a grouping function to ignore null values

Returns the Min value of the employee number

sql> Select min (empno) from EMP;

Returns the maximum value of an employee's salary

Sql> select Max (sal) from EMP;

The total wages issued by the company this month

sql> Select SUM (comm) +sum (SAL) from EMP;

sql> Select sum (NVL (sal,0) +NVL (comm,0)) from EMP;

Group BY clause

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.

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;

Illegal use of group functions

1. Columns that are used in the select list and not in the group function must be included in the GROUP BY clause.

Example:

Sql> Select Empno,count (Job) from EMP;

The correct wording is as follows:

Sql> Select Empno,count (Job) from the EMP group by Empno;

2, 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

HAVING clause

The HAVING clause sets the condition for the GROUP by clause in the same way that the WHERE clause interacts with 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

Grammar:

SELECT column, Group_function

From table

[WHERE condition]

[GROUP by Group_by_expression]

[Having group_condition]

[Order by column];

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;

Note: Grouping functions can be nested

Summary: The syntax format of the entire query statement is as follows:

SELECT