Using Cube

Contact Us or call 1-877-932-8228
Using Cube

Using Cube

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/roll_up2.sql
SELECT 
region_name, first_name,  count(*) 
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY rollup(region_name, first_name);

Although we see the summaries by region in this query, we do not see a summary by first name.

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/cube.sql
SELECT 
	region_name, 
	first_name,  
	count(*) 
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY cube(region_name, first_name);

By using CUBE rather than ROLLUP, summaries at both the region and first name level are returned.

Code Sample:

Enhancing-Groups-With-Rollup-and-Cube/Demos/cube2.sql
SELECT 
	decode(grouping(region_name),0,region_name,'SUMMARY') AS region_name, 
	decode(grouping(first_name),0,first_name,'TOTAL') AS first_name, 
	count(*)
FROM emp_details_view 
WHERE first_name LIKE 'Da%'
GROUP BY CUBE(region_name, first_name);

This query uses the GROUPING and DECODE functions to provide labels for the total lines.

Next