Group clause: (including: rollup, cube, group by grouping set)
ROLLUP - Description: Increases a summary bank for each group, packets each group of data (common AVG, SUM function)
- Example: Select Deptno, Decode (Job), SUM (SAL) from EmpGroup by Rollup (DEPTNO, JOB);
Cube
- Example: SELECT DECODE (GROUPING (department_name), 1, 'All Departments', department_name) AS department_name, DECODE (GROUPING (job_id), 1, 'All Jobs', job_id) AS job_id, COUNT (*) "Total Empl ", AVG (SALARY) * 12" Average Sal "from Employees E, Departments Dwhere D.Department_ID = E.DEPARTMENT_IDGROUP BY CUBE (Department_Name, Job_ID);
- Result: Department_name Job_id Total Empl Average Sal -------------------------------------------------------- --------------------- Accounting AC_ACCOUNT 1 99600Accounting AC_MGR 1 144000Accounting All Jobs 2 121800Administration AD_ASST 1 52800 ... All Departments ST_MAN 5 87360All Departments All Jobs 107 77798.1308
Group by grouping set ...
- Description: Multi-condition summary statement
- Example: SELECT channel_desc, calendar_month_desc, co.country_id, TO_CHAR (sum (amount_sold), '9,999,999,999') SALES $ FROM sales, customers, times, channels, countries coWHERE sales.time_id = times.time_idAND sales.cust_id = customers. cust_idAND sales.channel_id = channels.channel_idAND customers.country_id = co.country_idAND channels.channel_desc IN ( 'Direct Sales', 'Internet') AND times.calendar_month_desc IN ( '2000-09', '2000-10') AND co. Country_id in ('UK', 'US') Group by Grouping Sets ((Channel_Desc, Calendar_Month_Desc, Co.Country_ID), (Channel_Desc, Co.Country_ID), (Calendar_Month_Desc, Co.Country_ID);
