Tuesday, 15 January 2008

Oracle: ROLLUP

ROLLUP and CUBE are extensions to the GROUP BY functionality of the Select statement. The following select statement demonstrates basic use of ROLLUP:

select soh_cucode, soh_owner, sum(soh_net) from guser.f_sohdr
where soh_date between '01 JUN 2007' and '30 JUN 2007'
group by rollup (soh_cucode, soh_owner)

This statement will give output that looks like the following:

Within Customer Code the Grouping occurs via the Salesperson. For each Salesperson a Summary Total is shown and then finally a Total for that Customer, basically the sum of the values of each Salespersons orders. The bottom of the results set shows the Total Value of orders within the period for all Customers.

So basically the ROLLUP has worked like this:


"Not the power to remember, but its very opposite, the power to forget, is a necessary condition for our existence." Sholem Asch

No comments: