This document provides an example of using the SQL CUBE function to generate a cross-tabular report from employee data grouped by department and job. The CUBE function calculates subtotals for all combinations of the grouped columns as well as a grand total. The example output shows counts and salary sums calculated for each department, job, and in total.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online from Scribd
This document provides an example of using the SQL CUBE function to generate a cross-tabular report from employee data grouped by department and job. The CUBE function calculates subtotals for all combinations of the grouped columns as well as a grand total. The example output shows counts and salary sums calculated for each department, job, and in total.
This document provides an example of using the SQL CUBE function to generate a cross-tabular report from employee data grouped by department and job. The CUBE function calculates subtotals for all combinations of the grouped columns as well as a grand total. The example output shows counts and salary sums calculated for each department, job, and in total.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online from Scribd
This document provides an example of using the SQL CUBE function to generate a cross-tabular report from employee data grouped by department and job. The CUBE function calculates subtotals for all combinations of the grouped columns as well as a grand total. The example output shows counts and salary sums calculated for each department, job, and in total.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online from Scribd
Download as doc, pdf, or txt
You are on page 1/ 3
SELECT deptno, job, sum(sal) FROM emp GROUP BY ROLLUP(deptno,job); DEPTNO --------10 10 10 --> 10 20 20 20 --> 20
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 --------10 10 10 10 20 20 20 20 30 30 30 30 JOB COUNT(*) --------- --------CLERK 1 MANAGER 1 PRESIDENT 1 3 ANALYST 2 CLERK 2 MANAGER 1 5 CLERK 1 MANAGER 1 SALESMAN 4 6 ANALYST 2 CLERK 4 MANAGER 3 PRESIDENT 1 SUM(SAL) --------1300 2450 5000 8750 6000 1900 2975 10875 950 2850 5600 9400 6000 4150 8275 5000
