Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
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
  • 230 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