SQL HAVING Clause

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

SQL HAVING Example

We have the following "result" table:

Student_No	Subject_No	Year_Exam	Marks
ST101	SU03	1	69
ST102	SU01	1	61
ST101	SU04	1	70
ST101	SU05	1	87
ST103	SU01	2	51
ST103	SU03	1	59
ST103	SU01	3	56
ST108	SU01	1	78
ST105	SU05	2	98

Now we want to find if any of the student have a  average marks of less than 60.

We use the following SQL statement:

SELECT Student_No,AVG(Marks) 
FROM result 
GROUP BY Student_No 
HAVING AVG(Marks)<60;


The result-set will look like this:

Student_No	AVG(Marks)
ST103	55.3333

Now we want to find if the student "ST101" or "ST102" have a Average marks of more than 60.
We add an ordinary WHERE clause to the SQL statement:



SELECT Student_No,AVG(Marks) 
FROM result 
WHERE Student_No='ST101' OR Student_No='ST102' 
GROUP BY Student_No 
HAVING AVG(Marks)>60;



The result-set will look like this:

Student_No	AVG(Marks)
ST101	75.3333
ST102	61.0000