CODEWITHSUNDEEP
sqlt-sql
MonuMan5
MonuMan5

Reputation: 373

Noob Concern: Sub-query calculations

I've got a table bbc with the following columns:

name (refers to a name of a country within a particular region of the world)

region (continent of the world)

population (population of the country in the name name field) The question I'm trying to answer:

The question is as follows:
"Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions."

I was thinking the answer might be something like:

SELECT a.name, a.region FROM bbc AS a
WHERE a.region IN
     (
        SELECT b.region FROM bbc AS b 
        GROUP By b.region 
        HAVING MIN(b.population) < 3*b.population)

But honestly, I lose it at that last line... I have no idea how I would find counteries that have more than three times that of any of their neighbours in the same region! Quite tough. O_o

Any and all help would be appreciated.

Upvotes: 1

Views: 1547

Answers (3)

XINYAO LIU
XINYAO LIU

Reputation: 1

SELECT name, continent from world x
WHERE (SELECT population from world y
          where y.name = x.name 
           and y.continent = x.continent
           and population > 0) > ALL 
(SELECT 3*population from world z 
          where z.continent = x.continent 
             and z.name != x.name
          and population > 0);

Upvotes: -1

Laurence
Laurence

Reputation: 10976

Select
  a.name,
  a.region
From
  bbc as a
Where
  Not Exists (
    Select
      'x'
    From
      bbc as b
    Where
      a.region = b.region And
      a.name != b.name And
      a.population < 3 * b.population
  )

Upvotes: 0

roman
roman

Reputation: 117485

select
   a.name, a.region
from bbc as a
where 
    a.population >
    (
        select 3*max(b.population)
        from bbc as b
        where b.region = a.region and b.name <> a.name
    )

Upvotes: 2

Related Questions