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
- Having Clause – Microsoft Documentation
