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:

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 –

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.

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 –

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:

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 –

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

This is a guide to PL/ SQL having. Here we discuss the Introduction, syntax, Examples, and code implementation. You may also have a look at the following articles to learn more –

1. PL/SQL Cursor Loop
2. SQLite create index
3. PL/SQL Date Functions
4. SQLite Stored Procedures