Lesson : 14 - SELECT with HAVING clause

SELECT with HAVING clause

 

Syntax :

 

SELECT column1, column2, ... column_n, aggregate_function (expression)

FROM  <tablename(s)>  WHERE condition

GROUP BY column1, column2, ... column_n

HAVING condition1 ... condition_n;

 

Having clause can be  used to filter data selected on the aggregate  functions like count,sum,min,max etc..  This is same as  WHERE clause but is used with aggregate functions.

Note that,  Aggregate functions cannot be used with  WHERE Clause,  but can be used only with HAVING clause.

    

     Examples :

1)      To list out employee count of  all those departments whose employee count is more than 1

SQL> SELECT  dept,count(*) FROM emp GROUP  BY dept HAVING count(*) > 1;

 

2)      To list of  minimum salary of each department whose employee count is more than 10

SQL>SELECT dept, MIN(salary)  FROM emp GROUP BY dept  HAVING count(*) > 10;