php_novice2007 Posted August 27, 2007 Share Posted August 27, 2007 Hi all, I'm doing a question from my text book and I'm having trouble with the following question: Consider a table schema R(State, City, Popluation, GDP), express the query for "List all cities whose GDP is greater than the average of all State GDPs with population greater than 1 million" Make appropriate assumptions wherever necessary. So far I've coem up with: Select r1.City from R r1 where r1.GDP > (select r.State, avg(r.GDP) from R r where r.Populatioin > 1000000 group by r.State) I know this is wrong since my inner query should be returning a table of state and their average GDPs the the '>' operator is only for a single row... Thanks for any help~! Quote Link to comment https://forums.phpfreaks.com/topic/66827-how-to-express-this-query/ Share on other sites More sharing options...
Illusion Posted August 27, 2007 Share Posted August 27, 2007 Select city from R where gdp>(select avg(gdp) from R where population>100000); Quote Link to comment https://forums.phpfreaks.com/topic/66827-how-to-express-this-query/#findComment-335065 Share on other sites More sharing options...
php_novice2007 Posted August 27, 2007 Author Share Posted August 27, 2007 would select avg(gdp) from R where population>1000000 only give one number? I was thinking that all the cities might have popluation that is < 1000000 when you add them up and find out the popluation for the state if the sum is > 1000000 then get the average of teh GDP? Quote Link to comment https://forums.phpfreaks.com/topic/66827-how-to-express-this-query/#findComment-335159 Share on other sites More sharing options...
Illusion Posted August 27, 2007 Share Posted August 27, 2007 try this Select city from R where gdp>(select avg(gdp) from R where state=ANY(select if(sum(population)>100000, state,'xx') from R group by state)); there might be a better way but this what i got.......... why don't u go for some normalization so that it coule be very easy when u need to retrive the data like this............... Quote Link to comment https://forums.phpfreaks.com/topic/66827-how-to-express-this-query/#findComment-335268 Share on other sites More sharing options...
php_novice2007 Posted August 28, 2007 Author Share Posted August 28, 2007 gosh thats complicated :S Yeah this is just a question I'm trying to do from a text book so I don't have to real database or anything Guess they just want to chuck in a really hard question... Quote Link to comment https://forums.phpfreaks.com/topic/66827-how-to-express-this-query/#findComment-335962 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.