Please help me understand what I'm missing in this SQL query.

Question

Write a query to output the apartments whose prices are greater than the average and are also not rented, sorted by the 'Price' column.

I did:

Select * From Apartments
Where price > avg(price) And
Where status = 'not rented'
Order by price

I have tried this and rewritten it several times. I keep getting errors. Please help!

Answer 1

select * from Apartments
where price > (select avg(price) from Apartments)
order by price;

solves the code project for me... same answer as everyone is telling you ^^

Answer 2

Thank you everyone for all the help. I tried same answers typed the same way and it wasn't working. Then finally, I copied Alex's answer and it finally worked. I have no idea why it didn't work every other time, but thanks again for all the help!

Answer 3

-- 17 Code project Apartments

select *
from Apartments
where status = 'Not rented'
and price > ( select AVG(price) from Apartments)
order by price

Answer 4

Alex yep that's correct and will pass but the test cases were pretty bad
so you should also add a status check :)

Answer 5

Whoops... you're right, thanks :D

I'll leave that as a reminder that unit tests can overlook errors: D

Answer 6

Try a nested select for the avg:
where price > (select avg(price) from apartments)

Answer 7

Nope, unfortunately that did not fix the issue. I think it has something to do with where I put the AVG command, I'm just not sure

Answer 8

That didn't work either

Answer 9

Here:
select * from apartments where price > (select avg(price) from apartments) and status='Not rented'

(add the ORDER BY statement at the end because Sololearn wouldn't let me post an answer with it, somehow)

Answer 10

Narrow down the problem. Do not write the whole query at once, but layer the pieces one after the other. We neither know which DB you are using, nor whether the data is really what you suspect it is. Perhaps you have saved price as varchar? Then you need to parse it first. But we don't know, because we can't see the whole picture 🤷

All answers you received are basically giving the same solution.

Answer 11

This comes from lesson 17 code project

Answer 12

In complex terms: You can't use aggregate functions with WHERE clauses, you either have to use a subquery or a HAVING statement.

Here's more:
https://stackoverflow.com/questions/42470849/why-are-aggregate-functions-not-allowed-in-where-clause

Answer 13

Corey Karimian The SoloLearn SQL course is kinda finicky and doesn't have the intellisense and error reporting that SQL management studio would have. Breeze through the course as fast as you can and start using SSMS and the contoso test db as soon as possible for a more realistic experience.

Answer 14

Having will help to use the aggregate function for filter operation in sql. Try with it.