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 |
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) |
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)
|
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
|