+ 1

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

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!

17th Dec 2021, 9:06 PM
Corey Karimian
Corey Karimian - avatar
14 odpowiedzi
+ 3
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 ^^
18th Dec 2021, 3:12 AM
Alex
Alex - avatar
+ 5
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!
18th Dec 2021, 12:37 PM
Corey Karimian
Corey Karimian - avatar
+ 1
-- 17 Code project Apartments select * from Apartments where status = 'Not rented' and price > ( select AVG(price) from Apartments) order by price
18th Dec 2021, 1:07 AM
SoloProg
SoloProg - avatar
+ 1
Alex yep that's correct and will pass but the test cases were pretty bad so you should also add a status check :)
18th Dec 2021, 3:33 AM
Nikhil
Nikhil - avatar
+ 1
Whoops... you're right, thanks :D I'll leave that as a reminder that unit tests can overlook errors: D
18th Dec 2021, 3:42 AM
Alex
Alex - avatar
0
Try a nested select for the avg: where price > (select avg(price) from apartments)
17th Dec 2021, 10:09 PM
Alex
Alex - avatar
0
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
18th Dec 2021, 12:47 AM
Corey Karimian
Corey Karimian - avatar
0
That didn't work either
18th Dec 2021, 1:19 AM
Corey Karimian
Corey Karimian - avatar
0
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)
18th Dec 2021, 2:45 AM
Nikhil
Nikhil - avatar
0
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.
18th Dec 2021, 2:57 AM
Alex
Alex - avatar
0
This comes from lesson 17 code project
18th Dec 2021, 3:01 AM
Corey Karimian
Corey Karimian - avatar
0
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
18th Dec 2021, 4:09 PM
Anas MS
Anas MS - avatar
0
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.
18th Dec 2021, 7:23 PM
Kail Galestorm
Kail Galestorm - avatar
0
Having will help to use the aggregate function for filter operation in sql. Try with it.
19th Dec 2021, 5:40 PM
Mallikarjunagoud A
Mallikarjunagoud A - avatar