Shulou(Shulou.com)06/01 Report--

Grouping function: acts on a set of values and returns a value for a set of data

The use of grouping functions:

Commonly used grouping functions:

Avg (average), sum (summation), min (minimum), max (maximum), count (total), wm_concat (row-to-column)

Example: select count (distinct deptno) from emp

Line transfer: select deptno department number, the name of the employee in the wm_concat (ename) department from emp group by deptno

Grouping functions and null values: (grouping functions automatically ignore null values)

1. Statistics on the average salary of employees

Select sum (sal) / count (*) one, sum (sal) / count (sal) II, avg (sal) three from emp

two。 Count the average bonus of employees

Select sum (comm) / count (*) one, sum (comm) / count (comm) II, avg (comm) three from emp

Note: the nvl function makes it impossible for grouping functions to ignore null values

Selectsum (comm) / count (*) one, sum (comm) / count (nvl (comm,0)) two from emp

When Comm is empty, the nvl function returns 0, which is not null, so count will not ignore it.

The use of Group by clause

1. Ask for the department number and the average salary of the department

Selectdeptno,avg (sal) from emp

Group bydeptno

Abstract:

Selecta,b,c,avg (x) from table

Group bya,b,c

States that all columns in the select list that are not included in the group function should be included in the group by clause

Conversely, columns in group by do not have to be included in group functions

Grouping of multiple columns: (grouping logic: first grouped according to the first column after group by, if the first column is the same, then grouped according to the second column, if all columns are the same, then this is the data in the same group)

Statistics of the total salary of employees according to the department and different positions.

Select deptno,job, sum (sal) from emp

Group by deptno,job

Use of the Having clause: (filter grouped data)

Departments with an average wage of more than 2000

Select deptno,avg (sal)

From emp

Having avg (sql) > 2000

The difference between Where and having:

Grouping functions cannot be used after the where clause

Cases where Where and having can be common (without grouping functions, but from

From the point of view of Sql optimization, try to use where):

Inquire about the average salary of department 10

1. Where

Select deptno,avg (sal) from emp wheredeptno=10 group by deptno

2. Having

Select deptno,avg (sal) from empgroup by deptno having deptno=10

Use orderby sorting in grouping functions (default is asc ascending)

1.Select deptno, avg (sal)

From emp

Group by deptno

Order by avg (sal)

two。

Select deptno, avg (sal)

From emp

Group by deptno

Order by 2-2 here represents the second column after the select statement, which is avg (sal)

Nesting of grouping functions

Select max (avg (sal))

From emp

Group by deptno

Enhancements to Group by statements:

(1) Selext deptno,job,sum (sal)

From emp

Group by deptno,job

(2) Select deptno,sum (sal)

From emp

Having deptno

(3) Select sum (sal) from emp

Equivalent to (1) + (2) + (3)

Select deptno,job,sum (sal) from emp group byrollup (deptno,job)

Syntax:

Group by rollup (afort b)

Equivalent to: groupby a minute baked group by a+group by null

Break on deptno skip 2Mather-the same department number is displayed only once, between different department numbers.

Skip two lines

For more information, please take a look at the report function of sql-plus

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.