**Rollup function**

This blog gives a brief introduction to the usage of rollup in Oracle group function. Rollup function is commonly used in group statistics and is also a kind of Oracle analysis function

**Environmental preparation**

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

Business scenario: calculate the total salary of each department and the total salary of all departments

You can use union to do this. First, you can count the sum of wages by department, and then you can count the sum of wages of all departments

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

The above is done with union, and then with 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 and demand, now we need to look at the sum of salaries 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.job
Union 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.dname
Union all // sum of salaries of all departments
select null, null, sum(b.sal)
from scott.dept a, scott.emp b
where a.deptno = b.deptno;
```

Implemented with rollup, Simpler Syntax

```
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 time statistics, 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 detailed statistics of dimensions, and the syntax is similar to that of rollup

Suppose there are n dimensions, then there will be n aggregations for roll up and 2n aggregations for cube.

Rollup statistics column

The roll up (a, b) statistical column includes: (a, b), (a), ()

The roll up (a, B, c) statistical column includes: (a, B, c), (a, b), (a), ()

….

Cube statistics column

The statistical columns of cube (a, b) include: (a, b), (a), (b), ()

The statistical columns of cube (a, B, c) include: (a, B, c), (a, b), (a, c), (B, c), (a), (b), (c), ()

….

**summary**

The above is the whole content of this article. I hope that the content of this article has some reference learning value for your study or work. Thank you for your support for developepaer.