Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle Having Clause

The Oracle HAVING Clause is used with GROUP BY clause, because aggregate functions could not be used with WHERE keyword. The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified condition.

Oracle HAVING CLAUSE Syntax

To filter groups based on a specified condition, use the following syntax:

Syntax

SELECT col1, col2, ...,
aggregate_function (col)   
FROM tablename  
[WHERE conditions]  
GROUP BY col1, col2, ...  
HAVING condition
ORDER BY col1[ASC|DESC], col2[ASC|DESC], ...;  

Parameters:

tablename: The table name from which you want to fetch the records.

condition: To specify the conditions that must be fulfilled for the records to be selected.

HAVING: To sort data set in ascending order.

ORDER BY: To specify filter conditions for a group of rows or aggregates. It shows only those groups in result set whose conditions are TRUE.

aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

Oracle HAVING CLAUSE example with Count function

The following Oracle statement is used to filter groups based on a specified condition:

Example

SELECT COUNT(firstName), address
FROM tblcustomerloan
GROUP BY address
HAVING COUNT(firstName) > 1
ORDER BY COUNT(firstName) DESC;

Note:

In the above example, we have fetched records using filter groups based on a specified condition using HAVING.

Oracle HAVING CLAUSE example with Max function

The following Oracle statement is used to filter groups based on a specified condition:

Example

SELECT Max(workHover), address
FROM tblcustomerloan
GROUP BY address
HAVING Max(workHover) > 10

Note:

In the above example, we have fetched records using filter groups based on a specified condition using HAVING.