Oracle grouping function rollup, cube

Oracle grouping function rollup, cube
Rollup (Field 1, Field 2 ):
Cube (Field 1, Field 2 ):
Rollup (col1, col2 ,...) and cube (col1, col2 ,...) the usage difference lies in the cube in the record set summarized by rollup, and the summary of fields such as col2 is also added;
Www.2cto.com ROLLUP only summarizes the first parameter (field). CUBE can summarize the parameters (fields) in sequence. Therefore, only one parameter in ROLLUP takes effect (and the parameter is ranked first ).
In addition to the most basic syntax, Oracle group by statements also support ROLLUP and CUBE statements.
ROLLUP (A, B, C ):
First, group by is performed on (A, B, C), then group by is performed on (A, B), and then GROUP BY is performed on (, finally, perform the group by operation on the entire table.
CUBE (A, B, C), group by (A, B, C) is first performed, followed BY (A, B), (A, C ), (A), (B, C), (B), (C), and finally perform the GROUP BY operation on the entire table. The difference between CUBE and ROLLUP is:
The result set generated by CUBE displays the aggregation of all the combinations of the values in the selected column.
The result set generated by ROLLUP displays the aggregation of a certain hierarchy of values in the selected column. Example: [SQL] create table student (cgrade varchar2 (64), cclass varchar2 (64), cgroup varchar2 (64), stu int) [SQL] insert into student (cgrade, cclass, cgroup, stu) values ('1', '1', '1', 10); insert into student (cgrade, cclass, cgroup, stu) values ('1', '2', '1', 10); insert into student (cgrade, cclass, cgroup, stu) values ('1', '2 ', '2', 20); insert into student (cgrade, cclass, cgroup, stu) values ('2', '1', '1', 30 ); insert into student (cgrade, cclass, cgroup, stu) values ('2', '2', '2', 40); [SQL] select * from student; [SQL] select cgrade, cclass, sum (stu) from student group by cgrade, cclass; [SQL] select cgrade, cclass, sum (stu) from student group by cube (cgrade, cclass); [SQL] select decode (grouping (cgrade), 1, 'Students, 0, cgrade), decode (grouping (cclass) + grouping (cgrade), 1, 'Number of grades', 0, cclass), sum (stu) from student group by rollup (cgrade, cclass );