|
Group By Cube
The GROUP BY CUBE clause
produces a subtotal line for
each department number, and
calculates a total for each job
in each department, a grand
total at the end of the query,
and a total for each job in the
following example. The GROUP BY
ROLLUP does not return the
totals for each job, but does
return everything else like the
GROUP BY CUBE.
SQL> SELECT DEPTNO, JOB,
COUNT(*)
FROMEMP
GROUP BY CUBE(DEPTNO,JOB);
DEPTNO JOB COUNT(*)
---------------------------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
10 3
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
|
|
Use the
Oracle CUBE Function
This group by cube SQL will
use the CUBE operator to calculate the aggregates
and reproduce aggregates for each publisher, and the
grand total aggregates at the end of the report.
col pub_name
format a16
col book_type format a15
col
nbr_titles format 999,999
col sum_sold
format 999,999
break on pub_name
SELECT
pub_name,
book_type,
count(*) nbr_titles,
sum(quantity)
sum_sold
FROM
publisher
natural join
book
natural join
sales
GROUP BY
CUBE(pub_name, book_type)
Create cross-tabular reports with CUBE
In
multidimensional jargon, a "cube" is a
cross-tabulated summary of detail rows.
CUBE enables a SELECT statement to calculate
subtotals for all possible combinations of a
group of dimensions. It also calculates a grand
total. This is the set of information typically
needed for all cross-tabular reports, so CUBE
can calculate a cross-tabular report with a
single select statement.
Note in the
example below that totals are calculated for
each department, and also for each job category.
SELECT
deptno,
job,
count(*),
sum(sal)
FROM
emp
GROUP BY
CUBE(deptno,job);
DEPTNO JOB
COUNT(*) SUM(SAL)
---------
--------- --------- ---------
10 CLERK
1 1300
10 MANAGER
1 2450
10 PRESIDENT
1 5000
10
3 8750
20
ANALYST
2 6000
20 CLERK
2 1900
20 MANAGER
1 2975
20
5 10875
30
CLERK
1
950
30 MANAGER
1 2850
30 SALESMAN
4 5600
30
6 9400
ANALYST
2 6000
CLERK
4 4150
MANAGER
3 8275
PRESIDENT
1 5000
SALESMAN
4 5600
14 29025
Related Oracle CUBE Articles:
Understanding Oracle OLAP Dimensions and Cubes
Analytical Oracle SQL functions - rollup - cube
Inside Oracle cubes
Oracle
SQL Tuning and CBO Internal - Group By Cube
Oracle SQL Tuning and CBO Internal - Group By Rollup
Using the Oracle CUBE Function
ALL_CUBES view tips |
and include the URL for the page.
