Oracle Reporting 2 - Subtotals and Grand Total

Oracle provides rollup, cube, and grouping sets extensions to group to calculate subtotals and grandtotals. Each one fits into different scenarios. To start with, let's take a look at the following query.
select channel_id, sum(amount_sold) amount_sold
from sales
group by channel_id;

What if I want the grant total as the last row? 
select decode(grouping(channel_id), 1, 'Grand-Total', to_char(channel_id)) channel_id, 
sum(amount_sold) amount_sold
from sales
group by rollup(channel_id);

In this query, I used grouping() function and rollup extension to group by.
Grouping
Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBE operation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.
Rollup
ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. To further illustrate the rollup extension. Let's examine this query:
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);

In here, there're three levels of aggregations:
Group by deptno, job
Group by deptno
Grand total.
Keep in mind that the order of columns in the group by extension does matter. See:
select deptno, job, sum(sal)
from emp
group by rollup(job, deptno);

The aggregations here are - group by job, deptno, group by job and grand total. Now, let's move on to grouping sets. With grouping sets, user is able to specify the aggregation levels.
select deptno, job, sum(sal)
from emp
group by grouping sets((job, deptno), (job), (deptno))
order by deptno;

The aggreagations here are: group by (job, deptno), group by job, group by deptno. And here's no grand total. Oracle calculates the aggregations specified in the grouping sets extension, no more and no less.
Finally, let's take a look at cube extension:
select deptno, job, sum(sal)
from emp
group by cube(deptno, job)
order by deptno;

If user specifies cube, Oracle calculates all possible aggregations. Here, they're group by (job, deptno), group by job, group by deptno and grand total.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/638844/viewspace-777448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/638844/viewspace-777448/