Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

Oracle SQL code assistance

SQL

  • Please log in to reply

#1
nathanmw

nathanmw

    New Member

  • Member
  • Pip
  • 1 posts

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);

  • 0

Advertisements


#2
Angoid

Angoid

    Malware Expert

  • Expert
  • 233 posts

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!

  • 0






Similar Topics


Also tagged with one or more of these keywords: SQL

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP