Oracle9i Study Notes 15th Enhanced Group by clause

1. Review of group and group functions

Select [column,] group_function (column )...
From table
[Where condition]
[Group by group_by_expression]
[Order by column];

Example 1: calculate the average salary, number of employees with commission income, and the latest employment date of employees in each department whose job starts with Cl.

Select AVG (SAL), count (Comm), max (hiredate)
From EMP
Where job like 'cl % ';

Example 2: query the EMP table and display the number of employees with commission income, including the department ID, job position, total salary, and number of employees by department ID and job group.
Select deptno, job, sum (SAL), count (Comm)
From EMP
Group by deptno, job;

2. A review of having clauses

Select [column,] group_function (column )...
From table
[Where condition]
[Group by group_by_expression]
[Having having_expression]
[Order by column];

For example, query the EMP table and group by Department number. The average salary and average salary of a department with an average salary higher than $2000 are displayed.

Select deptno, AVG (SAL)
From EMP
Group by deptno
Having AVG (SAL) & gt; 2000;

3. rollup operation

Select [column,] group_function (column )...
From table
[Where condition]
[Group by [rollup] group_by_expression]
[Having having_expression]
[Order by column];

1) The rollup group generates a result set containing the rows and subtotal values of the regular group.
2) rollup is an extension of a group by clause.
3) using rollup operations to generate subtotal and Accumulation
 
Example:
Select deptno department_id, job job_id, sum (SAL) Salary
From EMP
Where deptno <60
Group by rollup (deptno, job );
Result:
Department_id job_id salary
10 3000
10 clerk 1300
10 manager 2450
10 President 5000
10 11750 // dept10 salary Subtotal
20 clerk 3100
20 analytic 6000
20 manager 2975
20 12075 // dept20 salary Subtotal
30 clerk 950
30 manager 2850
30. Salesman 5600
30 9400 // dept30 salary Subtotal
33225 // sum of all salary

4. cube operation

Select [column,] group_function (column )...
From table
[Where condition]
[Group by [cube] group_by_expression]
[Having having_expression]
[Order by column];

1) Cube is an extension of the Group by clause.
2) A cube group is a result set that generates a rollup row and a cross table row.

Example:
Select deptno department_id, job job_id, sum (SAL) Salary
From EMP
Where deptno <60
Group by cube (deptno, job)
Order by 1;
Result:
Department_id job_id salary
10 clerk 1300
10 manager 2450
10 President 5000
10 3000
10 11750
20 analytic 6000
20 clerk 3100
20 manager 2975
20 12075
30 clerk 950
30 manager 2850
30. Salesman 5600
30 9400
Analytic 6000
Clerk 5350
Management 8275
President 5000
Salesman 5600
3000
33225

5. Grouping Function

Select [column,] group_function (column), grouping (expr)
From table
[Where condition]
[Group by [rollup] [cube] group_by_expression]
[Having having_expression]
[Order by column];

1) The grouping function can be used for both cube operations and rollup operations.
2) use the grouping function to simulate and discover groups that constitute a subtotal in a row.
3) The grouping function returns 0 or 1.
 
Example:
Select deptno deptid, job, sum (SAL ),
Grouping (deptno) grp_dept,
Grouping (job) grp_job
From EMP
Where deptno <50
Group by rollup (deptno, job );

Result:
Deptid job sum (SAL) grp_dept grp_job
10 3000 0 0
10 clerk 1300 0 0
10 manager 2450 0 0
10 President 5000 0 0
10 11750 0 1
20 clerk 3100 0 0
20 analyst 6000 0 0
20 manager 2975 0 0
20 12075 0 1
30 clerk 950 0 0
30 manager 2850 0 0
30 salesman 5600 0 0
30 9400 0 1
33225 1 1

14 rows have been selected.

6. grouping set
1) grouping sets are further extensions of the Group by clause.
2) multiple groups can be defined in the same query using grouping sets.
3) the Oracle server calculates all groups specified in the grouping sets clause.
4) Efficiency of grouping sets:
-Only one query is performed on the base table.
-Complex Union statements are not required.
-Grouping sets have more elements for better execution performance.

Example:
Select deptno department_id, job job_id,
Mgr manager_id, AVG (SAL) Salary
From EMP
Group by grouping sets (deptno, job), (job, MGR ));
Result:
Department_id job_id manager_id salary
10 3000
10 clerk 1300
10 manager 2450
10 President 5000
20 clerk 1550
20 analytic 3000
20 manager 2975
30 clerk 950
30 manager 2850
30. Salesman 1400
3000
Clerk 7698 950
Clerk 7782 1300
Clerk 7788 1100
Clerk 7902 2000
Analyst 7566 3000
Manager 7839 2758.33333
Salesman 7698 1400
President 5000

19 rows have been selected.

7. Composite Columns
1) A composite column is a set of columns processed as a whole.
-Rollup (A, (B, c), d)
2) To specify a composite column, the group by clause is used to group the columns in parentheses. Therefore, the Oracle server performs Rollup
Or they are processed as a whole during cube operations.
3) When rollup or cube is used, Composite Columns will skip aggregation at a specific level.

Example:
Select deptno, job, Mgr, sum (SAL)
From EMP
Group by rollup (deptno, (job, MGR ));
Result:
Deptno job Mgr sum (SAL)
10 3000
10 clerk 7782 1300
10 manager 7839 2450
10 President 5000
10 11750
20 clerk 7788 1100
20 clerk 7902 2000
20 analyst 7566 6000
20 managers 7839 2975
20 12075
30 clerk 7698 950
30 manager 7839 2850
30. Salesman 7698 5600
30 9400
33225

Exercise
1. query the EMP table, group by Department number, job number, and manager number, and use rollup to query the total salary.

Select deptno, job, Mgr, sum (SAL)
From EMP
Group by rollup (deptno, job, MGR );

2. query the EMP table, group by Department number, job number, and manager number, and use cube to query the total salary.

Select deptno, job, Mgr, sum (SAL)
From EMP
Group by cube (deptno, job, MGR );

3. Write a query using grouping sets and display the following groups:
Deptno, Mgr, job
Deptno, job
Mgr, job
Queries and calculates the total wages of each group.

Select deptno, job, Mgr, sum (SAL)
From EMP
Group by grouping sets (deptno, job, MGR), (deptno, job), (MGR, job ));