EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/ SQL having
Secondary Sidebar
WordPress vs Wix

Web Services Interview Questions

Spring framework Interview Questions

Orphaned Case Java

Elasticsearch Interview Questions

HTML5 Interview Questions

PL/ SQL having

PL/ SQL having

Introduction to PL/ SQL having

PL/ SQL having clause helps us to determine if the current set or list has the specified word of character or any number of characters forming a particular string in the supplied source string. It is mostly used along with GROUP BY clause because we have to filter out the results in such a way that the final result will only contain the groups made up of rows and columns that satisfy the constraint specified in Having clause and contains the string or character specified in it. In this article, we will have a look at the syntax of having a clause, its syntax, usage, and implementation along with the help of certain examples.

Syntax:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

The syntax of the having clause is as shown below –

SELECT column1, column2, …, column n
FROM source_table
GROUP BY constraint for grouping the rows of source table
HAVING condition that needs to be satisfied by group
In the above syntax the clauses used are as explained below –
Column 1, column 2, …. Column n –

These are the number of columns that you wish to retrieve in the result set for the grouped rows. This may include the names of columns or expressions made up of using the aggregated functions on those columns.

  • Source_table – This is the name of the table from which we wish to retrieve the data and whose rows and contains we want to group and further apply the constraint to those groups by using having clause.
  • The constraint for grouping the rows of the source table – This can be any parameter or column (s) on the basis of which we want to prepare the groups from the table rows. This will be the column on the basis of which the groups will be created.
  • the condition that needs to be satisfied by a group – This is the constraint that you are specifying for groups. These constraints and conditions should be satisfied by the group that is created in order to include that group’s result in the final result.

Note: When we use HAVING clause without the GROUP BY clause then it will function in the same way as that of WHERE clause because in that case the constrain should be satisfied by the individual rows and not only the groups as there will be no groups created for the final result set. The basic difference between the HAVING and WHERE clauses is that HAVING applies restriction or constraint on the groups of rows while WHERE applies the constraint on the rows of the table.

Example

Let us try to consider the usage and implementation of the HAVING clause along with the help of examples. Firstly, lets us have a look at the test data.

Test data-

Consider one table whose name is customers_details that stores the information about all the customers and their contact details. The contents of the table can be checked by using the following query statement –

SELECT * FROM [customers_details];

The output of the execution of the above query statement is as shown below which shows the rows contained by customers_details table –

image 2

We will consider this table to study the implementation of HAVING clause in PL/ SQL.

Example #1

The most common usage of HAVING clause is when we make the use of GROUP BY clause along with it. Hence, let us try to retrieve all the total sales made at each of the stores. For this, we will make the use of the following query statement in which we will group the customer details on the basis of store id in the group by the clause –

SELECT
store_id,
SUM(bill_amount ) AS " Total Sales "
FROM
customers_details
GROUP BY
store_id
ORDER BY
store_id DESC;

The output of the above query is as shown below showing all the stores and the total sales at their respective store.

having

Now, if we want to get only those records having stores with a total bill amount greater than 25000. In that case, we can make the use of the HAVING clause to put up this constraint in groups of storage ids and our query statement will become as shown below –

SELECT
store_id,
SUM ( bill_amount ) AS " Total Sales "
FROM
customers_details
GROUP BY
store_id
HAVING SUM ( bill_amount ) > 25000
ORDER BY
store_id DESC;

The output of the execution of the above query statement will be as shown below displaying only those store ids whose total sales is greater than 25000 as applied by us in HAVING clause –

image 3

Example #2

Now consider the same table and the case where we want to retrieve the salesperson’s id and the total sales are done by that salesperson. For retrieving this summarized information we will make the use of SUM() aggregate function for adding all the bill amounts and will group them on the basis of the salesman_id column as we want to retrieve only the salesman and the sales done by each. Our query statement will become somewhat shown below –

SELECT salesman_id, SUM ( bill_amount) as "Total sales by salesperson" FROM [customers_details] GROUP BY salesman_id;

The output of the execution of the above statement is as shown below:

pl sql having

Now, if we want, we can apply whatever restriction we want on this grouped result to filter the rows. For example, if we want to find out low performing salesman, we can put up a condition saying retrieve only salespersons whose sales are below a certain threshold limit by making the use of having a clause in it. Let us try to find out only those salesperson whose total sales is less than 15000. Then our query statement will modify to as shown below:

SELECT salesman_id, SUM ( bill_amount) as "Total sales by salesperson"
FROM [customers_details]
GROUP BY salesman_id
HAVING SUM ( bill_amount) < 15000;

The output of the above statement is as shown below –

pl sql having 1

Conclusion

We can make the use of HAVING clause to specify the conditions, restrictions, and constraints on the groups created by using the GROUP BY clause. Most of the time, we make use of HAVING along with GROUP. In case, if we are using HAVING clause without GROUP BY then it behaves in the same way as that of WHERE clause in the query.

Recommended Articles

We hope that this EDUCBA information on “PL/ SQL having” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL Cursor Loop
  2. SQLite create index
  3. PL/SQL Date Functions
  4. SQLite Stored Procedures
Popular Course in this category
PL SQL Training (4 Courses, 2+ Projects)
  4 Online Courses |  2 Hands-on Projects |  17+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more