HAVING Clause is used for filtering like WHERE Clause. The difference is that while WHERE Clause is filtering on ungrouped data, HAVING Clause filters on grouped data. I recommend you read the following article about Group By.
“Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server”
Let’s remember the example in the article about Group By.
Example:
First, we create a table with the help of the following script and add a few records into this table.
|
1 2 3 4 5 6 7 8 9 10 |
USE [TestDB] GO CREATE TABLE [dbo].[ProductTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [ProductName] [varchar](250) NOT NULL, [ProductPrice] [int] NOT NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[ProductTable] VALUES ('Television',3400), ('Television',4300),('Phone',6700),('Phone',9800) |
Group By Clause:
We group the data by ProductName and calculate the total price of the products in the same group with the following query.
|
1 2 |
SELECT [ProductName],SUM(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
HAVING Clause:
By filtering the above query with HAVING Clause, we return the groups with the SUM (ProductPrice) value below than 10000 as a result.
|
1 2 3 |
SELECT [ProductName],SUM(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] HAVING SUM(ProductPrice)<10000 |
Using the HAVING Clause as in the example above, we can filter out groups that are grouped by GROUP By.
WHERE Clause and HAVING Clause in same SQL Statement:
We can use WHERE Clause and HAVING Clause in the same SQL Statement. The following example first reads data from the records in the table that has a ProductPrice value below 6800. This result set is then grouped according to ProductName. Lastly, it returns the groups with the SUM (ProductPrice) value below than 10000 as a result by filtering this result set with HAVING Clause.
To see all records in the table, we read the entire table first.
|
1 2 3 4 5 6 |
Select * FROM [dbo].[ProductTable] SELECT [ProductName],SUM(ProductPrice) FROM [dbo].[ProductTable] WHERE [ProductPrice]<6800 GROUP BY [ProductName] HAVING SUM(ProductPrice)<10000 |
