Oracle: Having Clause

What is Having Clause in Oracle?

Before jumping into this topic, you need to have a clear understanding of Group By Clause. Click here to study Group By Clause.

  • Having Clause in Oracle is always used in combination with Group By Clause.
  • As the Group By clause in Oracle is used to make groups with the use of aggregate functions, the Having Clause is used to restrict Group by function to provide values only if they satisfy the conditions.
  • In other words, the Having Clause is used to filter the Groups made by Group by clause as per the condition given. 

Syntax

HAVING [condition]
Group by Column_name;

Example

we have a Table Stu as shown below:

Stu Table

Now Let's understand Having Clause in two ways:

1. Grouping using Group By Clause

Select Subject, Count(*)
from Stu
Group By Subject;

The above code will return the total number of times, different subject categories, contained in the table.

Follow the output below to see how the above code actually works:

Output

Group by

From the above output, you got to know that we have a total of 5 types of subjects. Along with that, the total number of those subjects occurring is also displayed.

2. Applying HAVING Clause

Now, you want to filter the output attached below and what to know only the name of those subjects that are occurring more than 1 time in a table. Follow the code for the same:

Select subject, count(*)
from Stu
HAVING count(*)>1
Group BY Subject

Output

Having Clause