1

I've have a question to solve:

-list the names of countries where no city has a population over 1,000,000.

(I would think that this would mean that COUNT(name)should pull up names that are equal to zero. I'm not sure how to phrase that statement. Here is my code, there are two tables joined.

<mysql>SELECT ct.code, ct.fullname, COUNT(name) FROM city AS cy JOIN country AS ct ON cy.country = ct.code 
WHERE cy.population < 1000000 
GROUP BY ct.code 
ORDER BY ct.code ASC; 

+------+---------------------------------------+-------------+
| code | fullname                              | COUNT(name) |
+------+---------------------------------------+-------------+
| ABW  | Aruba                                 |           1 |
| AFG  | Afghanistan                           |           3 |
| AGO  | Angola                                |           4 |
| AIA  | Anguilla                              |           2 |
| ALB  | Albania                               |           1 |
| AND  | Andorra                               |           1 |
| ANT  | Netherlands Antilles                  |           1 |
| ARE  | United Arab Emirates                  |           5 |
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57

2 Answers2

1
SELECT cc.name
from city c, country cc
where CountryCode = code
group by cc.name
having MAX(c.population) < 1000000;
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • 1
    Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 09 '18 at 18:49
0
  • Count() used in your query will simply count all the cities in a country with population less than 1000000. Where clause basically returns only those cities which match your given condition (population less than 1000000). It does not necessarily imply that all the cities of a country are below 1000000.
  • You need Group By with Having clause, in order to check on all the cities of a country, such that none of them have population >= 1000000

Try the following query: 

SELECT ct.code, 
       ct.fullname 
FROM city AS cy 
JOIN country AS ct ON cy.country = ct.code 
GROUP BY ct.code, ct.fullname 
HAVING SUM(cy.population > 1000000) = 0 
ORDER BY ct.code ASC;
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • Thanks I'll try that. –  Oct 14 '18 at 12:42
  • I like the answer, but. just had the change the operators. The question was "where no city", I would think this would mean the less than operator.• < less than, But it seems to work. –  Oct 14 '18 at 12:58
  • It seems the answers may be off: I have two tables I am working with city.csv and country.csv. You should be able to fork them under my data sets. https://www.kaggle.com/hashtagkehl/citycsv https://www.kaggle.com/hashtagkehl/countrycsv The question that I'm trying to answer is: -list the names of countries where no city has a population over 1,000,000. the results. +------+-----------+ | code | fullname | +------+-----------+ | GIN | Guinea | | HKG | Hong Kong | | SGP | Singapore | | URY | Uruguay | +------+-----------+ 4 rows in set (0.01 sec) –  Oct 14 '18 at 13:34
  • @ChrisKehl sql statement used ? – Madhur Bhaiya Oct 14 '18 at 13:35
  • SELECT ct.code, ct.fullname -> FROM city AS cy -> JOIN country AS ct ON cy.country = ct.code -> GROUP BY ct.code, ct.fullname -> HAVING SUM(cy.population < 1000000)= 0 -> ORDER BY ct.code ASC; –  Oct 14 '18 at 13:37
  • results:+------+-----------+ | code | fullname | +------+-----------+ | GIN | Guinea | | HKG | Hong Kong | | SGP | Singapore | | URY | Uruguay | +------+-----------+ 4 rows in set (0.01 sec) –  Oct 14 '18 at 13:38
  • @ChrisKehl please check my answer. I am using `>` instead of `<`. I am actually using a negative condition to filter out. That is why you previously misunderstood as well. Basically if no city has population (`> 1000000`), the sum would be zero, and that is what I am checking for. – Madhur Bhaiya Oct 14 '18 at 13:39