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
SELECT Student_No,AVG(Marks)
FROM result
GROUP BY Student_No
HAVING AVG(Marks)<60;
The result-set will look like this:
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:
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:
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
|
Post a Comment