SQL HAVING clause
In all relational databases we are not allowed to use WHERE clause after GROUP BY clause to overcome this problem in case of this one Ansi/iso SQL provided another clause i.e. HAVING clause.
Generally if you want to restrict rows in a table then we are using WHERE clause where as if you want to restrict groups after GROUP BY clause then we must use HAVING clause.
Generally we can also use group functions in HAVING clause where as we are not allowed to use group functions in WHERE clause.
SELECT columnname,..... FROM tablename WHERE condition GROUP BY columnname HAVING condition ORDER BY columnname
1) Write a query to display those departments, sum(sal) having more than 10000 from emp table by using HAVING clause.
SQL> Select deptno,sum(sal) from emp GROUP BY deptno HAVING sum(sal) > 10000;
DEPTNO SUM(SAL) ---------- ---------- 20 10875
2) Write a query which is used to display year and number of employees per year in in which more than 1 employee was hired from emp table by using GROUP BY......HAVING clause.
SQL> select to_char(hiredate,'yyyy') "Year",count() from emp GROUP BY to_char(hiredate,'yyyy') HAVING count() >1;
Year COUNT(*) ---- ---------- 1987 2 1981 10
In all relational databases we can also use invisible group functions in having clause because when ever we are displaying group function values by using GROUP BY clause then database servers internally stores all other group function values in that report.
3) Write a query to display those departments sum of salary having more than 3 employees from emp table by using GROUP BY....HAVING clause.
Select deptno,sum(sal) from emp GROUP BY deptno HAVING count(*)>3;
DEPTNO SUM(SAL) ---------- ---------- 30 9400 20 10875