SQL: Not Using Aggregate Function in WHERE Clause, instead, Using HAVING Clause

A - Introduction

This is a tricky interview question for SQL. The content of the article will be:

• A - Introduction
• B - Question
• C - Solution
• D - Conclusion

B - Question

There is a Table: employee

There are two SQL Statement, such as

and

Question: which SQL Statement is more effecient performancewise?

C - Solution

This is tricky question, if you simply answered anyone of them is better than another, then you would fail. The fact is one of them, the first one, is not valid SQL statement. We run the first one in SSMS:

We got error message:

"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Instead, an Aggregate might be written in SELECT clause or FROM Clouse or following Group By with Having Clause.

Filtering data according to the result of an aggregate function is a common data analysis task. The aggregate functions include

Transact-SQL provides the following aggregate functions [ref]:

• APPROX_COUNT_DISTINCT
• AVG
• CHECKSUM_AGG
• COUNT
• COUNT_BIG
• GROUPING
• GROUPING_ID
• MAX
• MIN
• STDEV
• STDEVP
• STRING_AGG
• SUM
• VAR
• VARP

D - Conclusion

Not using aggregate functions in WHERE clause, instead, using a HAVING Clause.

 

Reference

• GROUP BY (Transact-SQL) - SQL Server | Microsoft Learn
• GROUP BY Statement
• HAVING (Transact-SQL) - SQL Server | Microsoft Learn
• Aggregate Functions (Transact-SQL) - SQL Server | Microsoft Learn