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.

Syntax:

SELECT columnname,.....
FROM tablename
WHERE condition
GROUP BY columnname
HAVING condition
ORDER BY columnname

Example:

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;

Output:

    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;

Output:

Year   COUNT(*)
---- ----------
1987          2
1981         10

Note:

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;

Output:

    DEPTNO   SUM(SAL)
---------- ----------
        30       9400
        20      10875