An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause

Question

0.00/5 (No votes)

See more:

Select EPA.tax_year, EP.house_number + ' ' + EP.street_name 'Property Addres', EPA.assessed_value "Assessed Value"
from EdmontonPropertyAssessment EPA
		Inner Join EdmontonProperty EP
			On EPA.account_number = EP.account_number
Where EPA.tax_year = '2022' and EPA.assessed_value = MAX(assessed_value)

What I have tried:

When I removed the EPA.assessed_value it's printing all of the year 2022 what i'm trying to do is select the highest assessed value in year 2022. It is for homework and I just need to use subquery in order to answer it

Posted 16-Mar-23 10:27am

Wakatoshiii

Updated 16-Mar-23 10:44am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-03-16T10:44:00

The error message is pretty explicit: you can't use an aggregate in a WHERE clause without a GROUP BY function with a HAVING clause: SQL HAVING Clause[^]

Me? I'd split is and select the MAX value into a variable, then use the variable in the comparison - it would also be a lot more efficient!

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause

1 solution

Solution 1

Add your solution here

Preview 0

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us