SQL Having Clause – A Comprehensive Guide

Filed Under: SQL
SQL HAVING CLAUSE (1)

Hi, folks. In this article, we will be focusing on SQL Having Clause in detail.


The need for the Having Clause

The Having clause adds a restriction to the type and amount of data presented by any SQL code block/query. In general, the Having clause guides the SELECT statement to represent only those data values that satisfy a certain condition stated by it.

Now, a question may arise in your mind that when we have the option to go for SQL WHERE clause, then why has the Having clause been introduced?

Well, let me answer this query of yours!

SQL WHERE clause does restrict the data values by applying a condition to the data columns. But, SQL WHERE clause does not work with SQL Aggregate functions.

This is when the Having clause came into the picture. Having clause works along with the aggregate functions to apply the conditions on the SELECT statement.


Syntax of the SQL Having

Having understood the necessity of the Having clause, let’s get started with the working and implementation of the same.

SELECT columns 
from Table
GROUP BY column
HAVING condition;

SQL Having clause needs to be placed after the GROUP BY clause. The Having clause applies the stated condition only on the groups formed by the column’s data mentioned through the GROUP BY clause.

Further, if we do not mention GROUP BY clause before Having clause, then Having clause works like a normal SQL WHERE clause.


Examples of SQL Having Clause

Let us now understand the working of the Having clause against various examples using different conditions.


The Having clause with COUNT() function

In the below example, we have used count() function as a condition to the Having clause. So, according to it, only those data values of column ‘id’ and ‘city’ would be selected, which would be grouped by the column ‘city’ and would have satisfied the Having clause’s condition.

Example:

select COUNT(id),city 
from Info
GROUP BY city
HAVING COUNT(id) = 1;

Output:

1	Mumbai
1	Satara
1	USA

Having clause with SUM() function

Example:

select SUM(Cost),city 
from Info
GROUP BY city
HAVING SUM(Cost)>50;

Output:

97	Mumbai
165	Pune

SQL Having clause with MAX() function

Here’s an article about the max() function, if you want to learn more about it.

Example:

select MAX(Cost),city 
from Info
GROUP BY city
HAVING MAX(Cost)>50;

Output:

97	Mumbai
100	Pune

SQL Having clause with AVG() function

Let’s try the having clause with the avg function

Example:

select AVG(Cost),city 
from Info
GROUP BY city
HAVING AVG(Cost)>50;

Output:

97.0000	Mumbai
82.5000	Pune

Using Having Clause with Multiple Conditions

The Having clause can be used with multiple conditions along with it using SQL AND, OR operators, etc.

Example 1:

In this example, we have used SQL AND operator to combine two conditions within the Having clause.

select AVG(Cost),city 
from Info
GROUP BY city
HAVING AVG(Cost)>50 AND AVG(Cost)<95;

Output:

82.5000	Pune

Example 2:

Here, we have made use of SQL OR operator to combine two conditions under the Having clause.

select AVG(Cost),city 
from Info
GROUP BY city
HAVING AVG(Cost)>20 OR AVG(Cost)<95;

Output:

97.0000	Mumbai
82.5000	Pune
50.0000	Satara
12.0000	USA

SQL Having Clause with ORDER BY Clause

The Having clause can be associated with SQL ORDER BY clause using the below command:

select columns 
from Table
GROUP BY column
HAVING condition
ORDER BY column;

Example:

select AVG(Cost),city 
from Info
GROUP BY city
HAVING AVG(Cost)>20 OR AVG(Cost)<95
ORDER BY AVG(Cost);

Output:

12.0000	USA
50.0000	Satara
82.5000	Pune
97.0000	Mumbai

Conclusion

By this, we have come to the end of this topic. Please go through SQL JournalDev for posts related to such topics.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages