In this tutorial, we’ll be discussing a few ways in which we can use or execute the HAVING Clause in SQL aggregate functions. Using this clause in SQL aggregate functions can greatly help in data summarization.
HAVING Clause is almost similar to WHERE statement and can also be executed along with it.
We’ll discuss their correlations and differences by providing examples as we go through this tutorial.
GROUP BY And WHERE Statements In SQL
For our first example, we’ll be discussing how to execute the WHERE clause and GROUP BY using the sample table below. Please note that we can only use the WHERE clause with existing columns and not in aggregate functions alone.
Let’s say we want to get a product’s Total Sales based on its SaleAmount column where the value is greater than 2. In order to get the result, our command should be the following:
First, we select the ProductName and aggregate the SaleAmount to get the TotalSales.
Then, we used the WHERE statement here since we’re trying to get only the products where the SaleAmount is greater than 2. The command ‘GROUP BY ProductName’ indicates that it will group rows under the column ProductName into one.
By executing our set of commands, we’ll see that out of all the data under the ProductName and SaleAmount columns, only the Bulb and Fan were displayed. That’s because they were the only ones having a SaleAmount that’s greater than 2.
In this example, the WHERE statement is for filtering results with an existing column, which is our SaleAmount. Also, note that the WHERE statement appears before the GROUP BY and can’t be used to filter aggregate functions. On the other hand, HAVING clause appears after the GROUP BY and is used for filtering based on an aggregate function.
GROUP BY And HAVING Clause in SQL
In this example, we’re going to demonstrate the difference between the WHERE statement and HAVING clause. We’ll use the same objective we have in the previous example so that we can compare and evaluate the results.
Let’s first display products with their total sales using the command below. As you can see, our first command is still the same as our first command executing the WHERE statement.
If we’re to execute our first two commands, we’ll have the following result:
Now, let’s say we only want to display products whose total sale is more than 5. We can’t use the WHERE statement since it can only be used with existing columns. Therefore, we need to use HAVING clause since we’ll be filtering from an aggregate function.
Notice how we use HAVING clause after GROUP BY unlike the WHERE statement being used before GROUP BY. This is because the SQL will group records before it evaluates the HAVING clause.
Let’s then execute those commands along with HAVING SUM(SaleAmount)>5. After specifying that we want to get only those products whose total price is greater than 5, we’ll notice that in our current table of results, we won’t see the Pen anymore. That’s because its total is less than 5.
In brief, when we want to filter data from our table based on an existing column, we use the WHERE clause, whereas when we want to filter data from an aggregate function, we use the HAVING clause.
HAVING And WHERE Clause In SQL Server Management Studio (SSMS)
Now, we’ll move forward by discussing and demonstrating how we can execute the HAVING clause in SQL Server Management Studio (SSMS). We’ll also tackle the difference between the HAVING clause and WHERE clause by providing examples.
Shown below is our sample data SalesOrderHeader. This data consists of 100 rows. For our example, we want to get TotalSale by CustomerID where TotalSale is greater than 10000. Note that the total sale is based from the sum of the TotalDue column values.
First, let me demonstrate why we can’t use the WHERE clause when we want to filter on an aggregate function based on our example above.
We’ll be using the sample set of commands from the image below. As you can see, we technically have the same set of commands as the one we have from our very first example.
However, when we click on Execute in the upper-left corner, it will result in an error because we cannot use the WHERE clause alone when we’re filtering on the aggregate function.
To correct the error, we need to filter the result using the HAVING clause instead of the WHERE clause. Our new set of commands should be similar to the image below.
Now, we can see that our error has been corrected and contains the results per CustomerID with a TotalSale greater than 10000.
Again, the WHERE clause is always used before the GROUP BY clause, while the HAVING clause is always used after a GROUP BY clause.
Using Both HAVING Clause And WHERE Clause In SQL
For this example, let’s say we want to use the WHERE and HAVING clause at the same time. Let’s try getting the TotalSale by CustomerID where TotalSale is greater than 10000 but only with customers where their TerritoryID is equal to 1.
Since we want to filter results with customers that have 1 as their TerritoryID, we’ll use the WHERE clause. So our command should be the same as our previous one. Again, we’ve added a WHERE clause before the GROUP BY clause.
For now, we can’t see much difference between our previous result and this new one. However, if you’ll look closely at the lower-right corner, we now only have 64 rows compared to the previous one with 505 rows of data. That’s because the results are filtered based on their TerritoryID as well.
***** Related Links *****
ISNULL SQL Function In Case Statements
SQL SELECT Statement: An Introduction
Common SQL Table Expressions For Power BI Users
Conclusion
To summarize, we can only use the WHERE clause with existing columns. If we need to filter with aggregated functions, we need to use the HAVING clause instead.
Using the HAVING clause in SQL aggregate functions and practicing ways to execute the HAVING clause and WHERE clause together can provide users convenience when working on a large number of data or records.
I hope I’ve provided you with enough information and understanding regarding how to use the HAVING clause in SQL aggregate functions. If you’d like to know more about this topic and other related content, you can certainly check out the list of relevant links below.
All the best,
Hafiz
