GROUP BY & HAVING
| GROUP BY AND HAVING |
GROUP BY
Using group by, we will create groups of related information.
Columns utilized in select must be used with group by, otherwise it had been not a gaggle by expression.
| SQL> select deptno, sum(sal) from emp group by deptno;
DEPTNO SUM(SAL) ———- ———- 10 8750 20 10875 30 9400 |
SQL> select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL) ———- ——— ———- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 |
HAVING
This will work as where clause which may be used only with group by due to absence of where clause in group by.
| SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000;
DEPTNO JOB TSAL ———- ——— ———- 10 PRESIDENT 5000 20 ANALYST 6000 30 SALESMAN 5600 |
SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) >3000 order by job;
DEPTNO JOB TSAL ——— ——— ———- 20 ANALYST 6000 10 PRESIDENT 5000 30 SALESMAN 5600 |
ORDER OF EXECUTION
1. Group the rows together supported group by clause.
2. Calculate the group functions for each group.
3. Choose and eliminate the groups supported the having clause.
4. Order the groups supported the required column.
Questions
 Visit Today : 23 |
 Visit Yesterday : 55 |
 This Month : 1388 |
 This Year : 28578 |