0

I have the following code (see below) that finds the minimum date of birth of an employee.

I don't understand why does it throw the error:

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.

The following code throws the previous error:

SELECT *
  FROM [TerpsConsultant.Employee] e1
  WHERE EXISTS (
SELECT *
  FROM [TerpsConsultant.Employee] e2
  WHERE e1.empDateOfBirth = MIN(e2.empDateOfBirth)
)

enter image description here

After searching for similar questions on here, I tried this code and it worked fine:

SELECT *
  FROM [TerpsConsultant.Employee] e1
  WHERE EXISTS (
SELECT *
  FROM [TerpsConsultant.Employee] e2
  WHERE e1.empDateOfBirth = (SELECT MIN(e2.empDateOfBirth) FROM [TerpsConsultant.Employee] e2)
)

Would you help me understand why the first version of the code is not working? What's the difference after all?

Dale K
  • 25,246
  • 15
  • 42
  • 71
beginner321
  • 1
  • error message told you the reason dy... aggregate function should be in subquery if you want to use it in where condition – learning Dec 19 '22 at 04:05
  • Where condition is performed before the row aggregates such as MIN or MAX are evaluated. Therefore at the time of performing the filter (where) the MIN can't yet be evaluated ( its scope is not known). When you put it in a subquery, the 'WHERE` clause on that subquery -or the absence of it- defines its scope, and it can be evaluated, and then used in the be main query. – tinazmu Dec 19 '22 at 04:16

1 Answers1

0

As others already said in comments, you cannot use aggregate function in where clause.

As specified in MSDN Aggregate Functions you can only use aggregate functions as expressions only in the following situations:

  • The select list of a SELECT statement (either a subquery or an outer query).
  • A HAVING clause.

So in your first statement, your code is invalid, because it doesn't meet the requirements for using aggregates. While in second statement, you have a subquery which returns the aggregate, and only then it checks on the where condition.

And as Sergey Kalinichenko answered on this old post.

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works correctly.

Shmiel
  • 1,201
  • 10
  • 25