There are just two relations important for this:

1. geoLake with Name and Country
2. geoMountain with Name and Country

Both relations having couple hundreds of Entries.

The Task is to just display the names of the countrys which have more lakes than mountains.

SELECT m.Country, Count(m.Country)
FROM geoMountain m
GROUP BY m.Country

Shows a list with all Countrynames and the Number of how many Mountains are related to each country.

SELECT l.Country, Count(l.Country)
FROM  geoLake l
GROUP BY l.Country

Having the same Output for how many Lakes are in every Country.

I tried like everthing to bring this two grouped relations together, but not having any success and kinda stucked after like 2 hours, because I am running out of ideas.

How do I bring this together?

My specific Questions:

Is it possible to get a Relation like:

 -------- ------------------- ---------------- 
|Country |COUNT(m.Country)   |COUNT(l.Country)|
 -------- ------------------- ---------------- 
|Country1|How many Mountains |How many Lakes  |
|Country2|How many Mountains |How many Lakes  |
[...]

And how do I add a SELECT query on top of this with this

SELECT Country FROM What is build up there WHERE COUNT(m.Country)  > COUNT(l.Country)

mechanic.

PS. Hope my question is understandable, english isn't my native language.

CodePudding user response：

WITH
-- count the amount of mountains
cte1 AS (
    SELECT m.Country, Count(m.Country) cnt
    FROM geoMountain m
    GROUP BY m.Country
),
-- count the amount of lakes
cte2 AS (
    SELECT l.Country, Count(l.Country) cnt
    FROM  geoLake l
    GROUP BY l.Country
),
-- gather countries which are mentioned in at least one table
cte3 AS (
    SELECT Country FROM cte1
    UNION
    SELECT Country FROM cte2
)
-- get needed data
SELECT Country, 
       COALESCE(cte1.cnt, 0) AS MountainsAmount,
       COALESCE(cte2.cnt, 0) AS LakesAmount
-- join data to complete countries list
FROM cte3
LEFT JOIN cte2 USING (Country)
LEFT JOIN cte1 USING (Country)
-- HAVING MountainsAmount > LakesAmount
;