HAVING - Apply Filtering Criteria at Group Level in SQL Server

Q

How To Apply Filtering Criteria at Group Level with The HAVING Clause in SQL Server?

✍: FYIcenter.com

A

Let's say you have divided the query output into multiple groups with the GROUP BY clause. Now you are only interested in some of the groups, not all the groups. If you want to filter out some groups from the query, you can apply filtering criteria at the group level by using the HAVING clause inside the GROUP BY clause with this syntax:

SELECT group_level_fields FROM source_tables
   WHERE search_condition
   GROUP BY group_by_expression
      HAVING group_filtering_condition

Since group_filtering_condition applies to groups, only group level expressions can be used in group_filtering_condition. The following tutorial exercise gives you some good examples of HAVING clause:

SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links 
   GROUP BY tag HAVING AVG(counts) > 300
GO
tag     COUNT(*) MIN(created)   AVG(counts)
DBA     3        2005-01-01     774
SQA     2        2003-01-01     778

SELECT tag, COUNT(*), MIN(created), AVG(counts) 
   FROM fyi_links 
   GROUP BY tag 
      HAVING AVG(counts) > 300 AND tag = 'DBA'
GO
tag     COUNT(*) MIN(created)   AVG(counts)
DBA     3        2005-01-01     774

Note that the more criteria you have in the HAVING clause, the less groups you will get.

 

Using SELECT Statements and GROUP BY Clauses in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-25, 496👍, 0💬