HAVING

The SQL HAVING clause allows us to restrict the data that is sent to the GROUP BY clause.
DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE:

1)Group functions cannot be used in the WHERE clause but can be used in the HAVING clause.

2)WHERE filters data before grouping and HAVING filters the data after grouping.

3)A WHERE clause is useful in both grouped and ungrouped queries, while a HAVING clause should appear only immediately after the GROUP BY clause in a grouped query.

NP:
1)HAVING allows a user to perform conditional tests on aggregate values. It is often used in combination with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group.

2)Aggregates cannot be used in a WHERE clause; they are used only inside HAVING.
3)Similar to the WHERE clause, the HAVING clause requires that the column names that appear in the clause must also appear as column names in the GROUP BY clause.
4)Similar to the WHERE clause, it is ok for column names not appearing in the GROUP BY clause to appear as arguments to aggregate functions.

Syntax:

The following is the position of the HAVING clause in a query:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

Example:

Here is the EMPLOYEE1 table:

ID	NAME	START_DATE	END_DATE	SALARY	CITY
1	A	01/01/2016 00:00:00	01/06/2016 00:00:00	10,000	BOMBAY
2	B	02/01/2016 00:00:00	07/03/2016 00:00:00	10,000	KOLKATA
3	C	03/01/2016 00:00:00	08/06/2016 00:00:00	10,000	CHENNAI
4	D	01/01/2016 00:00:00	07/06/2016 00:00:00	10,000	DELHI
5	E	01/01/2016 00:00:00	09/06/2016 00:00:00	12,000	NOIDA
6	F	01/01/2016 00:00:00	08/06/2016 00:00:00	15,000	BHUBANESWAR
7	G	01/01/2016 00:00:00	07/06/2016 00:00:00	11,000	PUNE
8	A	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	GOA
8	B	01/01/2016 00:00:00	06/06/2016 00:00:00	10,000	DELHI

1) Find the average salary of for each employee who has name starts with ‘A’ or ‘B’

SELECT NAME, (AVG(SALARY))

FROM DSOCIQA.EMPLOYEE1

GROUP BY NAME

HAVING NAME LIKE 'A'

This would produce the following result:

NAME	(AVG(SALARY))
A	10,000