SQL HAVING Clause

The HAVING Clause was added to SQL because the WHERE keyword could not be used with aggregate functions. As we know that WHERE clause is used to restrict the number of rows fetched from the table. But if we try to restrict the number of groups with where clause it will generate an error message. So we can use having clause to divide the groups with Group by clause.

The SQL HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. The HAVING clause behaves like the WHERE clause. But is to groups – the rows in the result set representing groups. In contrast the WHERE clause is applied to individual rows, nor to groups.

Syntax:

SELECT column1, column2, column_n, aggregate_function (expression) FROM tables WHERE predicates GROUP BY column1, column2,... column_n HAVING condition1.... e condition_n;

SQL> select deptno, sum (sal) “Total Salary” from EMP group by deptno having sum (sal) > 2000;

On execution it will further restrict the rows as shown above. Thus using the HAVING clause, we can filter the groups of rows returned from your GROUP BY query. The HAVING clause can only be used by the GROUP BY clause and it always contains an aggregate SQL function such as (SUM, AVG, MAX etc.)