Basic Oracle Tutorial: clustering, grouping, row-to-column Conversion

Multi-row function clustering function execution sequence: tName -- where -- groupby -- having -- orderby (select) where cannot contain aliases in the current clause or aggregate

Multi-row function clustering function execution sequence: tName -- where -- group by -- having -- order by (select) where cannot contain aliases in the current clause or aggregate

Multi-row function Aggregation Function
Execution sequence:
TName -- where -- group by -- having -- order by (select)

The alias in the current clause cannot appear in the where clause, nor can the aggregate (Group) function be used.

When clustering functions are nested, a single column cannot be obtained.

Common Aggregate functions
Returns a result after a group or batch of data is combined.
Count the total number of rows -- process null values, and the null values are included.
Count (distinct column)
Count (all column) all is the default parameter and can be left empty
Avg average -- null value not processed
Sum column value and -- null values are not processed
Max
Min minimum

Count ([{distinct | all} 'column name' | *) indicates that the time and space of the column value is not included in the statistics.
When the value is *, it contains empty and repeated rows.
Idle> select count (comm) from emp;

COUNT (COMM)
-----------
4

Idle> select count (ename) from emp;

COUNT (ENAME)
------------
14

Idle> select count (*) from emp;

COUNT (*)
----------
14

Idle>

Idle> select count (deptno) from emp;

COUNT (DEPTNO)
-------------
14

Idle> select count (distinct deptno) from emp;

COUNT (DISTINCTDEPTNO)
---------------------
3

Idle> select count (all deptno) from emp;

COUNT (ALLDEPTNO)
----------------
14

Idle>

Idle> select avg (sal), sum (sal), max (sal), min (sal), count (sal) from emp;

AVG (SAL) SUM (SAL) MAX (SAL) MIN (SAL) COUNT (SAL)
--------------------------------------------------
2073.21429 29025 5000 800 14

Idle>

The aggregate function executed above collects statistics on all records.
If you want to group statistics (such as the average value of the Statistical Department), you need to use group by to limit the results of group statistics, you need to use having filtering.
Group by group statistics 9I sorting 10 Gb unordered

Average salary for the same job in the same department
Select deptno, job, avg (sal) from emp group by deptno, job;

Calculate the average salary of each department

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

Deptno avg (SAL)
--------------------
30 1566.66667
20 2175
10 2916.66667

Idle>
Group and sort again
Idle> select deptno, avg (sal) from emp group by deptno order by deptno;

Deptno avg (SAL)
--------------------
10 2916.66667
20 2175
30 1566.66667

Idle>
The Group modifier column can be an unselected column.
Idle> select avg (sal) from emp group by deptno order by deptno;

AVG (SAL)
----------
2916.66667
2175
1566.66667

Idle>

The grouping function executed above collects statistics on all records. If you want to group statistics (such as the average value of the Statistical Department), you need to use group by to filter the results of grouping statistics using having.
Group by group statistics 9I sorting 10 Gb unordered

Find the average salary of no department

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

Deptno avg (SAL)
--------------------
30 1566.66667
20 2175
10 2916.66667

Idle>
Group and sort again
Idle> select deptno, avg (sal) from emp group by deptno order by deptno;

Deptno avg (SAL)
--------------------
10 2916.66667
20 2175
30 1566.66667

Idle>
The Group modifier column can be an unselected column.
Idle> select avg (sal) from emp group by deptno order by deptno;

AVG (SAL)
----------
2916.66667
2175
1566.66667

Idle>

If the grouping function is used in the query, any column or expression that is not in the grouping function must be in the group by clause.
The grouping function returns one row while other columns display multiple rows.
Idle> select avg (sal) from emp;

AVG (SAL)
----------
2073.21429

Idle> select deptno, avg (sal) from emp;
Select deptno, avg (sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group function

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

Deptno avg (SAL)
--------------------
30 1566.66667
20 2175
10 2916.66667

Idle> select deptno, avg (sal) from emp group by deptno order by job;
Select deptno, avg (sal) from emp group by deptno order by job
*
ERROR at line 1:
ORA-00979: not a group by expression

Idle>

Group by multi-condition grouping
SCOTT @ ora10g> select deptno, job, avg (sal), max (sal) from emp group by deptno, job order by 1;

Deptno job avg (SAL) MAX (SAL)
---------------------------------------
10 CLERK 1300 1300
10 MANAGER 2450 2450
10 PRESIDENT 5000 5000
20 ANALYST 3000 3000
20 CLERK 950 1100
20 managers 2975 2975
30 CLERK 950 950
30 MANAGER 2850 2850
30. SALESMAN 1400 1600

9 rows selected.

SCOTT @ ora10g>