Reporting Aggregated Data Using the Group Functions
- Grouping Functions
— Query to find the Average Salary,Count of Employees,Maximum Salary,Minimum Salary,Standard Deviation of Salary,Sum of Salary and Variance of Salary from EMP Table —
SELECT AVG(sal) “Average”,COUNT(*) “Count”,
MAX(sal) “Maximum”,MIN(sal) “Minimum”,
STDDEV(sal) “Standard Deviation”,SUM(sal) “Sum”,
VARIANCE(sal) “Variance”
FROM emp
/
SELECT SIN(sal)
FROM emp
/
SELECT ROUND(AVG(SAL)) “Average”,TRUNC(AVG(SAL)),COUNT(*) “Count”,
MAX(SAL) “Maximum”,MIN(SAL) “Minimum”,
STDDEV(SAL) “Standard Deviation”,SUM(SAL) “Sum”,
VARIANCE(SAL) “Variance”
FROM EMP
/
SELECT AVG(sal) “Average”,COUNT(deptno) “Dept Cnt”,COUNT(*) “Total Cnt”,COUNT(DISTINCT deptno) “Cnt Distinct Deptno”,
MAX(sal) “Maximum”,MIN(sal) “Minimum”,
STDDEV(sal) “Standard Deviation”,SUM(sal) “Sum”,
VARIANCE(sal) “Variance”
FROM emp
/
— Group Functions and NULL Values (GROUP Functions eliminate the NULL Values) —
SELECT AVG(COMM) “Average Comm without NULL”
FROM EMP
/
In order to forcefully include NULL in GROUP Function use the NVL Function —
SELECT AVG(NVL(COMM,0)) “Commission Avg with Null”
FROM EMP
/
- GROUP BY Clause and HAVING Clause
GROUP BY Clause —
— Query to display the Salary and Deptno sorted with Deptno —
SELECT sal,deptno FROM emp ORDER BY deptno
/
— Query to display the Sum Total of Sal for each Deptno —
SELECT DEPTNO,SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO
/
SELECT DEPTNO,SUM(SAL)
FROM EMP
GROUP BY DEPTNO
/
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno
ORDER BY deptno
/
— Columns in the Select Query should be included in the GROUP BY Clause. Cols selected for GROUP Functions need not be selected in GROUP BY Clause . —
SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno
/
SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno,sal
ORDER BY deptno
/
— Columns in the GROUP BY Clause need not be selected in the Select Expression —
SELECT SUM(sal)
FROM emp
GROUP BY deptno
ORDER BY deptno
/
— Use of GROUP Functions in ORDER BY Clause —
SELECT DEPTNO,SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY SUM(SAL)
/
— Grouping by more than one column —
SELECT SAL,DEPTNO,JOB FROM EMP ORDER BY DEPTNO
/
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO
/
— Illegal Queries using GROUP Functions —
SELECT DEPTNO,SUM(SAL)
FROM EMP
/
Any Col in the SELECT List that is not an aggregate function must be in the GROUP By Clause
This query will error out. So in order to rectify the Error the GROUP BY Clause has to added
SELECT DEPTNO,SUM(SAL)
FROM EMP
GROUP BY DEPTNO
/
HAVING —
— Do not include GROUP Functions in WHERE Clause of a SQL Query —
SELECT DEPTNO,SUM(SAL)
FROM EMP
WHERE SUM(SAL) > 8000
GROUP BY DEPTNO
/
This query will error out since filtering of GROUPS cannot be done using WHERE Clause.
In order to resolve this use the HAVING Clause
SELECT DEPTNO,SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 9000
/
— Sorting the rows based on the GROUP BY HAVING Clause —
SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000
ORDER BY SUM(sal) DESC
/
— Nesting GROUP Functions
— Query to display the Maximum Average Salary —
SELECT ROUND(MAX(AVG(sal))) Max FROM emp
GROUP BY deptno
/
SELECT ROUND(MAX(AVG(sal))) Max FROM emp
/
— This above SQL Query will error out because a Nested Group Function should have a GROUP BY clause —
SELECT AVG(sal),deptno FROM emp
GROUP BY deptno
/