Database Reference
In-Depth Information
Figure 11.16
The HAVING
Clause Can Use Its
Own Aggregate
Functions.
11.4.3.1
The ROLLUP Clause
Rollup is an extension to the GROUP BY clause and is used to create
super aggregates or groupings of groupings. The ROLLUP clause is com-
monly used to create subtotals plus a grand total, on multiple levels for all
columns listed in the GROUP BY clause. Totals are based on the order of
columns listed in the GROUP BY clause, moving through the GROUP
BY clause from the right to the left. Rollup can be very useful for creation
of summary tables or materialized views. The result of the next query is
shown in Figure 11.17.
Note:
The user-defined function GETTIME is described in Chapter 24
(PL/SQL). GETTIME is required for the next query.
CREATE OR REPLACE FUNCTION GETTIME(pTIME IN VARCHAR2)
RETURN NUMBER IS
vLEN INTEGER DEFAULT 0;
vSPLIT INTEGER DEFAULT 0;
vHOURS INTEGER DEFAULT 0;
vSECONDS INTEGER DEFAULT 0;
BEGIN
vSPLIT := INSTR(pTIME,':');
vLEN := LENGTH(pTIME);
vHOURS := TO_NUMBER(SUBSTR(pTIME,1,vSPLIT-1));
