Total Sum of oracle -- rollup and cube groups

The groupby Statement supports basic having conditions and provides information aggregation functions for rollup and cube, similar to subtotal. Rollup: vertical subtotal, which summarizes each column one by one from right to left and generates an independent row in the result. Only the statistical row of the column specified by the first grouping condition is returned. Cube: horizontal subtotal selectd. deptno, t. job, sum (t. sal)

The group by Statement supports basic having conditions and provides information aggregation functions for rollup and cube, similar to subtotal. Rollup: vertical subtotal, which summarizes each column one by one from right to left and generates an independent row in the result. Only the statistical row of the column specified by the first grouping condition is returned. Cube: horizontal subtotal selectd. deptno, t. job, sum (t. sal)

The group by Statement supports basic having conditions and provides information aggregation functions for rollup and cube, similar to subtotal.

Rollup: vertical subtotal, which summarizes each column one by one from right to left and generates an independent row in the result. Only the statistical row of the column specified by the first grouping condition is returned.

Cube: Subtotal

1. Select d. deptno, t. job, sum (t. sal) from emp t, dept d
2. Where t. deptno = d. deptno
3. Group by rollup (d. deptno, t. job)

Execution result:

***

Use grouping_id to display records at the specified group level. Returns the decimal value of the grouping () bit vector. The calculation method of the GROUPING bit vector is to combine the results of calling the GROUPING function for each column in order.

For example: grouping_id (a, B, c). If a is null, 0 is non-null, and B is the same as c. a three-digit number is returned, which is converted to decimal by binary, a, B, and c are all non-empty, that is, 111 is 7, and three columns are 7. If they are two columns, 11 is 3.

Select... from Table group by rollup (...) having grouping_id (...) <= 1

Rollup (x, y) Columns	Rollup (x, y, z) 3 columns
Total is grouping_id = 3	Total is grouping_id = 7
Subtotal grouping_id = 1	Subtotal grouping_id = 3
The record is grouping_id = 0	The record is grouping_id = 0

In this case, do not display the last total line:

1. Select d. deptno, t. job, sum (t. sal) from emp t, dept d
2. Where t. deptno = d. deptno
3. Group by rollup (d. deptno, t. job) having grouping_id (d. deptno, t. job) <= 1

Execution result:

In actual situations, we need to add subtotal and total to the summary columns in the result set. In this case:

Execution result: