SQL按两列分组并计数

Question

我有一个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的指针吗？

- Gordon Hickley

Answer 1

您可以使用相关子查询：

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

Demo here

- Giorgos Betsos

---

很抱歉，这个答案是重复的 - dcieslak

@dcieslak，相对于我之前发布的回答来说？ - Giorgos Betsos

无论如何，这太简单了 ;) 我可以称呼您为此查询的大师。 - dcieslak

这非常慢。 - Snow

Answer 2

您可以在结果的子查询中执行它。

  SELECT jm.country, jm.city, 
       count(city) as city_count,
       (select count(*) from jobs j where j.country = jm.country) as country_count
    FROM jobs jm
    GROUP BY jm.country, jm.city

SQL Fidlle example

- dcieslak

---

很抱歉，这是一个重复的答案。 - Giorgos Betsos

Answer 3

请使用自连接。

select a.country,a.city,b.city_count,c.country_count
from jobs a
inner join (select count(1) as city_count, city
    from jobs
    group by city
) b on a.city = b.city
inner join (select count(1) as country_count, country 
    from jobs
    group by country
) c on a.country = c.country
group by country,city

- Hotdin Gurning

---

这会导致错误。 - Snow

Query Error: error: column reference "country" is ambiguous - Snow

Answer 4

当时我已经写了答案，但Giorgos Betsos发布了与我的代码相同的答案，所以我也写了CTE的答案。

我们可以用很多方式得到结果，但这里有两个解决方案，第二个查询不太复杂。

declare @Table1 TABLE 
    ([country] varchar(7), [city] varchar(6))
;

INSERT INTO @Table1
    ([country], [city])
VALUES
    ('italy', 'milan'),
    ('italy', 'rome'),
    ('italy', 'rome'),
    ('ireland', 'cork'),
    ('uk', 'london'),
    ('ireland', 'cork')
;

第一种方法

   ;with CTE AS (
    select T.country,T.city,count(T.city)TCity from @Table1 T
    group by country,city )
    select C.country,C.city,C.TCity,T.T from CTE C INNER JOIN (select count(country)T,country from @Table1 group by country )T
    ON T.country = C.country
    GROUP BY c.country,c.city,c.TCity,t.t

第二种方式

SELECT country, city, count(city) as citycnt,
           (SELECT count(*)
            FROM @Table1 AS TT
            WHERE T.country = TT.country) AS countrycnt
    FROM @Table1 AS T
    GROUP BY country, city

- mohan111