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







No comments:

Post a Comment