mysql 两列计数,mysql-SQL分组依据和两列计数

我有一个MySQL表,数据为：-

country | city

---------------

italy | milan

italy | rome

italy | rome

ireland | cork

uk | london

ireland | cork

我想查询此信息并按国家和城市分组,并具有城市和国家的计数,如下所示：-

country | city | city_count | country_count

---------------------------------------------

ireland | cork | 2 | 2

italy | milan | 1 | 3

italy | rome | 2 | 3

uk | london | 1 | 1

我可以：-

SELECT country, city, count(city) as city_count

FROM jobs

GROUP BY country, city

这给了我：-

country | city | city_count

-----------------------------

ireland | cork | 2

italy | milan | 1

italy | rome | 2

uk | london | 1

是否也有指向country_count的指针？

解决方法:

您可以使用相关的子查询：

SELECT country, city, count(city) as city_count,

(SELECT count(*)

FROM jobs AS j2

WHERE j1.country = j2.country) AS country_count

FROM jobs AS j1

GROUP BY country, city

标签：sql,mysql,group-by

来源： https://codeday.me/bug/20191119/2037743.html