Generating Reports by Grouping related Data
- Review of Group Functions
- Group Functions operate on sets of rows to give one result per group.
SELECT AVG(SAL),STDDEV(SAL),COUNT(EMPNO),MAX(HIREDATE)
FROM EMP
/
2.
Review of the GROUP BY Clause
The GROUP BY Clause specifies how the rows should be grouped in the Table.
In the example, the Total Salary and no. of Employees are calculated for each job within each department.
The rows are grouped by the department no. and then grouped by job within each department.
SELECT deptno,job,SUM(sal),COUNT(empno)
FROM emp
GROUP BY deptno,job
/
Use the HAVING Clause along with GROUP BY to specify which Groups are to be filtered and displayed.
SELECT deptno,job,SUM(sal),COUNT(empno)
FROM emp
GROUP BY deptno,job
HAVING COUNT(empno) <= 3
/
3. GROUP BY with ROLLUP and CUBE Operators
- ROLLUP grouping produces a result set containing the regular grouped rows and subtotal values.
- CUBE grouping produces a result set containing the rows from ROLLUP and cross-tabulation rows.
— Rollup —
SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY deptno,job
/
SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno,job)
/
— Cube —
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING deptno = 10
/
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING deptno = 20
/
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING deptno = 30
/
SELECT job,SUM(sal) FROM emp GROUP BY job HAVING job = ‘CLERK’
/
SELECT job,SUM(sal) FROM EMP GROUP BY job HAVING job = ‘ANALYST’
/
SELECT job,SUM(sal) FROM emp GROUP BY job HAVING job = ‘MANAGER’
/
SELECT job,SUM(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘SALESMAN’
/
SELECT JOB,SUM(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT’
/
SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB
ORDER BY DEPTNO
/
— Example using CUBE — (Grouping for Deptno and Grouping for Job and Grouping for Deptno,Job)
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO,JOB)
/
— The Same Example using ROLLUP —
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB)
/
CUBE (a, b)
(a)
(b)
(a,b)
4. GROUPING Function
Grouping helps us to understand how a summary value has been obtained.
Grouping distinguishes super aggregate rows from regular grouped rows.
The GROUP BY extensions such as ROLLUP and CUBE produce super aggregate rows where the set of all values
is represented by NULL.
So using the GROUPING Function we can distinguish a NULL representing the set of all values in a super aggregate row.
The function returns a value of 1 if the value of the col in a row is null representing the set of all values otherwise it returns 0.
SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno,job)
/
SELECT deptno,job,SUM(sal),GROUPING(deptno),GROUPING(job)
FROM emp
GROUP BY ROLLUP(deptno,job)
/
SELECT deptno,SUM(sal),GROUPING(deptno)
FROM emp
GROUP BY ROLLUP(DEPTNO)
/
SELECT deptno,SUM(sal),GROUPING(deptno)
FROM emp
GROUP BY CUBE(deptno)
/
SELECT deptno,job,SUM(sal),GROUPING(deptno),GROUPING(job)
FROM emp
GROUP BY CUBE(deptno,job)
/
SELECT deptno,job,SUM(sal),
DECODE(GROUPING(deptno), 1, ‘All Departments’,0,’No Grouping’,deptno) AS department,
DECODE(GROUPING(job), 1, ‘All Jobs’,0,’No Grouping’,job) AS job
FROM emp
GROUP BY ROLLUP(deptno,job)
/
5. Grouping Sets
Used to define multiple groupings in the same query.
Grouping Sets is a further extension of GROUP BY clause that you can use to specify multiple groupings.
A single SELECT statement can be written using GROUPING SETS to specify various groupings rather than multiple SELECT statements combined by UNION ALL operators.
SELECT deptno,job,mgr,AVG(sal)
FROM emp
GROUP BY
GROUPING SETS
((deptno,job,mgr),
(deptno,mgr),(job,mgr))
/
This statement calculates aggregates over three groupings :
1.(department_id,job_id,manager_id),
2.(department_id,manager_id)
3.(job_id,manager_id)
Another alternative of this approach using UNION operators is
SELECT deptno,job,mgr,AVG(sal)
FROM emp
GROUP BY deptno,job,mgr
UNION ALL
SELECT deptno,NULL,mgr,avg(sal)
FROM emp
GROUP BY deptno,mgr
UNION ALL
SELECT null,job,mgr,avg(sal)
FROM emp
GROUP BY job,mgr
/
6. Composite Columns
A composite column is a collection of columns that are treated as a unit during the computation of groupings.
— Normal ROLLUP —
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno,job,mgr)
/
— Composite Column ROLLUP —
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno,(job,mgr))
/
ROLLUP ((job,mgr),deptno)
(job,mgr,deptno)
(job,mgr)
(Grand Total)
— Normal Cube —
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY CUBE(deptno,job,mgr)
/
— Composite Column CUBE —
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY CUBE(deptno,(job,mgr))
/
CUBE ((job,mgr),deptno)
(job,mgr,deptno)
(job,mgr)
(deptno)
(grand total)