[nathanmw]

Hi, I was hoping you all could help me with this.  I'm trying to list every country (and all the columns available in that table) that has a population higher than the average population and also speaks spanish ('es').  I have this code that I've tried different variations of, but it won't seem to work.  Any help?

 

select *

from countries

where language = 'es'

group by population

having population > avg(population);

[Advertisements]

Hmmm, long time since I last did any Oracle hacking.

 

Try this:

 

WITH popn AS
(SELECT language, population, AVG(population) over (partition by language) avpop
 FROM countries
 WHERE language = 'es'
)
SELECT *
FROM popn
WHERE population > avpop

 

I think this might also work (and do the same thing):

 

SELECT *
FROM (SELECT language, population, AVG(population) over (partition by language) avpop
 FROM countries
 WHERE language = 'es')
WHERE population > avpop

 

I'm assuming that what you're trying to do is list those Spanish-speaking countries where the population is greater than the average?

 

Unfortunately, I don't have access to Oracle / SQL*Plus so can't verify the above, but I think it's right .... try it and post back!

[Angoid]

Hmmm, long time since I last did any Oracle hacking.

 

Try this:

 

WITH popn AS
(SELECT language, population, AVG(population) over (partition by language) avpop
 FROM countries
 WHERE language = 'es'
)
SELECT *
FROM popn
WHERE population > avpop

 

I think this might also work (and do the same thing):

 

SELECT *
FROM (SELECT language, population, AVG(population) over (partition by language) avpop
 FROM countries
 WHERE language = 'es')
WHERE population > avpop

 

I'm assuming that what you're trying to do is list those Spanish-speaking countries where the population is greater than the average?

 

Unfortunately, I don't have access to Oracle / SQL*Plus so can't verify the above, but I think it's right .... try it and post back!