17 - SQL HAVING Clause

As you saw in the previous chapters, that you can group the records using GROUP BY clause. You can filter out records further using HAVING Clause. This clause works on group of rows rather than individual rows.

Syntax:

SELECT col1, col2,…colN from table_name
WHERE conditions
GROUP BY col1, col2…colN
HAVING condition
ORDER BY col1,col2..colN;

 

Example 1:

Suppose consider an example of sales department where there is a table containing details of sales by each  salesperson. Now let us say we want to have total sales per sales person wise but need to exclude data of 1 salesperson, because the data is not correct for that person and the result would not be correct if included.

 

Inv ID

Salesperson

Sales

1

Mike

12000

2

John

15000

3

Linda

10000

4

Tod

2000

5

Lex

15000

6

Mike

20000

7

Lex

20000

8

John

15000

SELECT SALESPERSON, SUM(SALES)    
FROM SALES_TABLE
GROUP BY SALESPERSON
HAVING SALESPERSON <> “TOD”
ORDER BY SALESPERSON;

Salesperson

Sales

John

30000

Lex

35000

Linda

10000

Mike

32000

 

Example 2:

Get the Minimum Sales Sales done by each salesperson but with condition that Minimum Sales >15000

SELECT SALESPERSON, MIN(SALES)
FROM SALES_TABLE
GROUP BY SALESPERSON
HAVING MIN(SALES) > 15000
ORDER BY SALESPERSON;

 

Salesperson

Sales

John

30000

Lex

35000

Mike

32000

HAVING Clause can be used in sub queries as well. HAVING clause is preceeded by GROUP BY clause, hence the HAVING clause acts as filter and restrict the groups created by the GROUP BY clause. Groups that don’t satisfy the condition are filtered out from the resultset.

If the HAVING Clause is used without using GROUP BY clause then set of rows from the WHERE Clause are considered as single group and condition in HAVING BY clause is applied for that group.

Example 3:

Get the employee details of the employees having SALES > 5000.

 

SELECT A.DEPT_ID, EMP_ID, EMP_NAME
FROM EMPLOYEE A
GROUP BY A.DEPT_ID
HAVING A.EMP_ID = (SELECT B.EMP_ID FROM SALES_TABLE
            WHERE SALES > 5000
AND A.EMP_ID = B.EMP_ID);

 

 

Like us on Facebook