SELECT Col1, FUNCTION(Col2) FROM dbo.table_name GROUP BY Col1 HAVING CONDITION
Let's see some examples.
I have a table named dbo.Books, which I’ll use in my examples here. Here’s the table. Create this table in your SQL Server database or you can use any other table that has some numeric values. Yes, the table must have number values, since you need to use Aggregate functions like SUM(), COUNT() etc.
Using HAVING clause in SQL Server
See the above table again. There are different categories (like Computer, Science etc.) with a price tag. I can use the SUM() function (an aggregate function) to get the total price of each category. Like this,
SELECT Category, SUM(Price) 'Total Price' FROM dbo.Books GROUP BY Category
It shows the sum of the price, grouped by the Category (or for each group).
Note: Since I am using a Column name (Category) with the aggregate SUM() functin in my query, I have to use the GROUP BY clause. Or else, it will throw an error.
Now, I want to filter it further. I want to get the total price for particular categories, whose total price is more than 150. So, if the sum of the price is more than 150, show it or else ignore it.
Here I need to use the HAVING clause.
SELECT Category, SUM(Price) 'Total Price' FROM dbo.Books GROUP BY Category HAVING SUM(Price) > 150
Another HAVING clause example and this time I am using the COUNT() function.
Remember, you can use the HAVING clause with any aggregate function.
SELECT COUNT(BookID) C, Category FROM dbo.Books GROUP BY Category HAVING COUNT(Category) >= 2
This above query returns categories, which have 2 or more rows in the table.
Using HAVING and WHERE clause together
This is another interesting and important query, which you’ll often use. Using the HAVING clause with the WHERE clause.
Although, both HAVING and WHERE are used to filter rows (using conditions) in a table, both have different usages. See this query.
SELECT Category, SUM(Price) 'Total Price' FROM dbo.Books WHERE Category IN ('Computers', 'Science', 'Programming') GROUP BY Category, Price HAVING SUM(Price) > 150
I am first filtering the Categories using the WHERE clause and later filtering it further using the HAVING clause.
Using HAVING clause with Multiple values
With multiple values, I mean multiple conditions. For example, now I want to use a range of values with HAVING and filter rows based on that range.
SELECT Category, SUM(Price) 'Total Price' FROM dbo.Books WHERE Category IN ('Computers', 'Science', 'Programming') GROUP BY Category HAVING SUM(Price) > 50 AND SUM(Price) < 200
The range I have set is more than 50 and less than 200.
Talking about multiple values in HAVING, you can also do this.
SELECT Category, SUM(Price) 'Total Price' FROM dbo.Books GROUP BY Category HAVING Category LIKE '%comp%' and SUM(Price) < 200
Hope this is useful. These are just few basic examples, however, it will you give a clear idea about using the HAVING clause in an SQL query.
Thanks for reading.☺