Good day! you need to calculate the average age of avg(age) and bring the city in which avg(age) > 40

since I don’t have a column with age and average age, but there is a column BirthDay I consider age and average age as follows: select city, avg(age) as AverageAge from (select city,DATEDIFF (YY, BirthDate, GETDATE()) -CASE WHEN DATEADD(YY, DATEDIFF(YY, birthdate, GETDATE()), birthdate) > GETDATE() THEN 1 ELSE 0 select city, avg(age) as AverageAge from (select city,DATEDIFF (YY, BirthDate, GETDATE()) -CASE WHEN DATEADD(YY, DATEDIFF(YY, birthdate, GETDATE()), birthdate) > GETDATE() THEN 1 ELSE 0 END AS [age] from Employees) x group by city

This way all cities and the average age are displayed in them, please tell where averageage > 40 how to set the condition where averageage > 40

  • one
    write this condition in HAVING - Mike
  • @Mike thanks !! - DoneBass

1 answer 1

 select city, avg(age) as averageAge from (select city, firstname, lastname, DATEDIFF (YY, BirthDate, GETDATE()) - CASE WHEN DATEADD(YY, DATEDIFF(YY, birthdate, GETDATE()), birthdate) > GETDATE() THEN 1 ELSE 0 END AS [age] from Employees) X group by city HAVING(avg(age)) > 60

all you had to add was HAVING (avg (age))> 60 as advised by @Mike

  • mark the answer as correct - Anatol