Hi.
I am trying to understand How Rollup works. This is the best explanation I found on the internet https://www.oracletutorial.com/oracle-basics/oracle-rollup/ , but unfortunately, I can not understand it completely. I wish you could help.
I tried the following query on Scott schema…
select deptno, job, sum(sal) from emp
where deptno in (10, 20)
group by rollup (deptno, job) ;
Here's the result but I need you tell me how it works in English.
I can see that it retrieves…
1- The same rows a group by (deptno, job) would retrieve.
2- It sums up salary of all rows belonging to each department.
3- a grand total.
Is that right? I think it's but if the query is about one column like that…
select deptno, sum(sal) from emp
where deptno in (10, 20)
group by rollup (deptno) ;
then I can not apply number 2 to it. I can not say, it sums up salary of all rows belonging to each department because it does not do that. It sums up salary of all rows for all departments.
2- The link says 2 other points which I do not understand too…
- Then, progressively create higher-level subtotals of the grouping columns, which are "job" and "deptno" columns, from right to left. – the first query.
What has “right to left” to do?!
2. It says that should be 3 levels of subtotals because there are 2 columns in the rollup expressions. Could you tell me what the three levels are.
Thanks in advance.