Groupby extended grouping function of Oracle

Oracle groupby extended grouping function test learning process is as follows: --- rollup: take each department as the reference object, details of employee salaries under each department and summary selectt.de

Oracle group by extended grouping function test learning process is as follows: --- rollup: take each department as the reference object, details of employees' salaries under each department and summary of select t.de

Group by function of Oracle

The test process is as follows :-
-- Rollup: Details and summary of employees' salaries in each department for reference.
Select t. dept_id, t. dept_name, sum (t. salary)
From qcfang. test1 t
Group by rollup (t. dept_id, t. dept_name)

-- Cube: Cross-report with the largest amount of information, including summary and details
Select t. dept_id, t. dept_name, sum (t. salary)
From qcfang. test1 t
Group by cube (t. dept_id, t. dept_name)

-- Rollup cancels the final total. In fact, it filters out records whose dept_id is null.
Select t. dept_id, t. dept_name, sum (t. salary)
From qcfang. test1 t
Group by t. dept_id, rollup (t. dept_name)
Order by t. dept_id

-- The cube cancels the total. In fact, it filters out records whose dept_id is null.
Select t. dept_id, t. dept_name, sum (t. salary)
From qcfang. test1 t
Group by t. dept_id, cube (t. dept_name)

-- Grouping settings: a summary of different values in each dimension, which is equivalent to multiple union all
Select t. dept_id, t. dept_name, sum (t. salary)
From qcfang. test1 t
Group by grouping sets (t. dept_id, t. dept_name)

-- Use the grouping function to find out the sum: flag = 1 is the sum.
Select t. dept_id, t. dept_name, sum (t. salary), grouping (t. dept_name) flag
From qcfang. test1 t
Group by rollup (t. dept_id, t. dept_name)
Order by t. dept_id

-- Filter groups using the grouping Function
Select t. dept_id, t. dept_name, sum (t. salary), grouping (t. dept_name) flag
From qcfang. test1 t
Group by rollup (t. dept_id, t. dept_name)
Having grouping (t. dept_name) = 0

-- Sort by grouping_id
Select t. dept_id, t. dept_name, sum (t. salary), grouping (t. dept_name) flag
From qcfang. test1 t
Group by rollup (t. dept_id, t. dept_name)
Order by grouping_id (t. dept_id, t. dept_name), t. dept_id

Recommended reading:

Oracle Lpad Functions

Oracle Regular Expression Functions

Oracle DB Group functions

Oracle DB uses conversion functions and conditional expressions

Learning window functions over () in Oracle

,