Oracle function-group function

Classification of functions

One-line function: an input corresponds to an output,input and there is an one-to-one correspondence between output,input and output, such as lower

Group function: multiple input, but only one output. Such as sum ()

1. Group functions (multiline functions) Group functions by default

SQL > select min (sal), max (sal), sum (sal), avg (sal) from emp where empno=10

2. Max min: count dates, numbers and strings

SQL > select min (hiredate), max (hiredate) from emp

SQL > select min (ename), max (ename) from emp

3. Count: for a count of the result set

① count (*): rows with empty statistical result sets

SQL > select count (*) from emp

② count (xxx): do not count rows that are empty in the result set (only non-empty rows that satisfy the expression)

SQL > select count (comm) from emp where deptno=30

SQL > select count (comm) from emp

③ count (distinct xxx): distinct- weeds out duplicate rows

SQL > select count (distinct deptno) from emp

4. Avg (xxx) to find the average.

SQL > select avg (sal) from emp

SQL > select avg (comm) from emp

SQL > select avg (nvl (comm,0)) from emp; counts everyone's average bonus

5. Group by: grouping

① single column grouping

SQL > select avg (sal) from emp group by deptno

The result set is meaningless: you need to precede it with a column name

SQL > select deptno,avg (sal) from emp group by deptno

SQL > select deptno,ename,avg (sal) from emp group by deptno

② multi-column grouping

First grouping departments, grouping the same work under the same department, and calculating the average?

SQL > select deptno,job,avg (sal) from emp group by deptno,job

Example of error:

SQL > select deptno,avg (sal) from emp

6. Having: filtering

After ① grouping, I still want to filter and ask that the average salary of the department is greater than xxx.

SQL > select deptno,avg (sal) from emp group by deptno

DEPTNO AVG (SAL)

--

30 1566.66667

20 2175

10 2916.66667

7. Conditional expression

① case

Select ename,job

Case

When deptno=10 then sal

When deptno=20 then 2*sal

When deptno=30 then 3*sal

Else sal/2

End new_sal

From emp

② decode

Select ename,job,decode (deptno,20,2*sal) from emp

Select ename,job,decode (deptno,20,2*sal,sal) from emp

Select ename,job,decode (deptno,10,sal,20,2*sal,30,3*sal,sal/2) from emp

③ only shows the salary of department 10, not the salary of department 10.

SQL > select case when deptno=10 then sal else 0 endfrom emp

SQL > select deptno,avg (sal) from emp group by deptno having avg (sal) > 2000