CODEWITHSUNDEEP
sqlpostgresql
jbmarks
jbmarks

Reputation: 1

SQL world database; finding regions with at least five countries that have a population over 10 million

I'm working with a database in pgAdmin 3 on a single table called 'country' that has five columns: name, region, area, population, gdp. Name is the name of the country. Region is the area in the world that country is in (for example, Europe, Middle East, Oceania, etc). Area, population, and gdp are attributes of that country.

I'm trying to select regions with at least 5 countries with population over 10 million.

I've figured out how to select countries with population over 10 million:

SELECT name 
FROM country 
WHERE population > 10000000

And I know how to count the number of countries in each region:

SELECT region, count(name)
FROM country
GROUP BY region

But for the life of me, I can't figure out how to somehow merge the two queries to get the result I want.

The closest I've gotten so far is finding the regions with at least 5 countries in them.

SELECT region, count(name)
FROM country
GROUP BY region
HAVING count(name) > 5

My question is how to create a query that shows regions 5 or more countries with a population over 10 million.

PS: I'm trying to do this without the creation of views, in a single query.

Upvotes: 0

Views: 1804

Answers (4)

LukStorms
LukStorms

Reputation: 29667

To select regions with at least 5 countries with population over 10 million?

Using a conditional COUNT in the HAVING clause can do that.

SELECT 
 region, 
 COUNT(*) AS TotalCountries,
 COUNT(CASE WHEN population > 10000000 THEN 1 END) AS TotalOverMillionCountries
FROM country
GROUP BY region
HAVING COUNT(CASE WHEN population > 10000000 THEN 1 END) > 5

Or just the regions without totals

SELECT region
FROM country
GROUP BY region
HAVING COUNT(CASE WHEN population > 10000000 THEN 1 END) > 5

Upvotes: 0

miciry89
miciry89

Reputation: 306

Did you try this?

SELECT region, count(name)
FROM country
WHERE population > 10000000
GROUP BY region
HAVING count(name) > 5

Upvotes: 1

Jo.........
Jo.........

Reputation: 330

Try this one.

SELECT region, count(name)
FROM country
WHERE population > 10000000
GROUP BY region
HAVING count(name) > 5

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

You could combine WHERE and HAVING:

SELECT region, count(name)
FROM country
WHERE population > 10000000
GROUP BY region
HAVING count(name) > 5

Upvotes: 1

Related Questions