HAVING Clause in SQL Server and Difference Between WHERE Clause(TSQL)

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.

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.

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.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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