GROUP BY mySQL statement

Question

using my SQL list, for each country, the number of cities. Would you think that this statement means count the number of cities of each country?

+----+----------------+---------+---------------+------------+
| id | name           | country | original-name | population |
+----+----------------+---------+---------------+------------+
|  1 | Kabul          | AFG     | Kabol         | 1780000    |
|  2 | Qandahar       | AFG     | Qandahar      | 237500     |
|  3 | Herat          | AFG     | Herat         | 186800     |
|  4 | Mazar-e-Sharif | AFG     | Balkh         | 127800     |
|  5 | Amsterdam      | NLD     | Noord-Holland | 731200     |
|  6 | Rotterdam      | NLD     | Zuid-Holland  | 593321     |
|  7 | Haag           | NLD     | Zuid-Holland  | 440900     |
|  8 | Utrecht        | NLD     | Utrecht       | 234323     |
|  9 | Eindhoven      | NLD     | Noord-Brabant | 201843     |
| 10 | Tilburg        | NLD     | Noord-Brabant | 193238     |
+----+----------------+---------+---------------+------------+

my code that works but it's over 4000 lines:

SELECT name, country 
FROM city 
GROUP BY name, country 
ORDER BY country ASC;

Lukasz Szozda · Accepted Answer · 2018-10-13 15:52:24Z

1

You need to GROUP BY country only:

SELECT country, COUNT(*) as count_of_cities_per_country
FROM city 
GROUP BY country 
ORDER BY country ASC;

answered Oct 13, 2018 at 15:52

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user9302886 Over a year ago

mysql> SELECT country, COUNT(*) as count_of_cities_per_country -> FROM city -> GROUP BY country -> ORDER BY country ASC -> ; +---------+-----------------------------+ | country | count_of_cities_per_country | +---------+-----------------------------+ | ABW | 1 | | AFG | 4 | | AGO | 5 | | AIA | 2 |

Collectives™ on Stack Overflow

GROUP BY mySQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related