How to use ROLLUP grouping functions in Oracle

In this issue, Xiaobian will bring you about how to use ROLLUP grouping function in Oracle. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

Environmental preparation

create table dept as select * from scott.dept;create table emp as select * from scott.emp;

Business Scenario: Find the sum of wages for each department and the sum of wages for all departments

Here you can use union to do it, first calculate the sum of wages by department, and then calculate the sum of wages in all departments.

select a.dname, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by a.dnameunion allselect null, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno;

The above is done with union, and then rollup, the syntax is simpler, and the performance is better.

select a.dname, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by rollup(a.dname);

Business scenario: Based on the above statistics, plus demand, now we need to look at the sum of wages corresponding to each department position.

select a.dname, b.job, sum (b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by a.dname, b.jobunion all//sum of salaries of all departments select a.dname, null, sum (b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by a.dnameunion all//sum of all departmental salaries select null, null, sum (b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno;

Rollup implementation, syntax is simpler

select a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by rollup(a.dname, b.job);

If you add a time statistic, you can use the following sql:

select to_char(b.hiredate, 'yyyy') hiredate, a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by rollup(to_char(b.hiredate, 'yyyy'), a.dname, b.job);

cube function

select a.dname, b.job, sum(b.sal) from scott.dept a, scott.emp b where a.deptno = b.deptno group by cube(a.dname, b.job);

cube

Functions are more fine-dimensional statistics, syntax similar to rollup

Assuming there are n dimensions, then rollup has n aggregations and cube has 2n aggregations.

rollup statistics column

rollup(a,b) statistical column contains: (a,b),(a),()

rollup(a,b,c) statistical column contains: (a,b,c),(a,b),(a),()

....

cube statistics column

cube(a,b) statistics column contains: (a,b),(a),(b),()

cube(a,b,c) statistical column contains: (a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()

The above is how to use ROLLUP grouping function in Oracle shared by Xiaobian. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.