在SQL中统计不同多列的出现次数

Question

我正在尝试在用户表中计算一组不同城市和国家的出现次数。

该表格的设置类似于：

userid  city       country
------  ---------  --------------
1       Cambridge  United Kingdom
2       London     United Kingdom
3       Cambridge  United Kingdom
4       New York   United States

我需要的是每个城市，国家对出现次数的列表。

Cambridge, United Kingdom, 2
London, United Kingdom, 1
New York, United States, 1

目前我运行一个SQL查询以获取不同的一对：

$array = SELECT DISTINCT city, country FROM usertable

然后在PHP中将其读入数组中，并循环遍历该数组，在每个数组行中运行查询以计算每个行的每个出现次数：

SELECT count(*) FROM usertable
WHERE city = $array['city']
AND   country = $array['country']

我假设我对SQL的理解不够深入，那么正确的方法应该是什么，最好不需要PHP的干预？

- Christian Mayne

Answer 1

select city, country, count(*)
from usertable
group by city, country

- Dawood ibn Kareem

Answer 2

你需要的是使用 group by：

Select city, country, count(*) as counter
from usertable
group by city, country

- aF.

Answer 3

SELECT cityandcountry, count(*) as occurrences FROM (
  SELECT DISTINCT concat(city, country) FROM tablename
) as baseview;

如果您想要格式化城市和国家，或者

SELECT cityandcountry, count(*) as occurrences FROM (
  SELECT DISTINCT city, country FROM tablename
) as baseview;

如果不是。

- Eugen Rieck

---

谢谢 - 我在MySQL中尝试了这个，但是出现了以下错误：'每个派生表都必须有自己的别名'。 - Christian Mayne

我认为这样做行不通。每个 count(*) 只会是 baseview 中行的总数，这不是 OP 想要的。 - Dawood ibn Kareem

@DawoodibnKareem，你看到了“DISTINCT”吗？它确保“baseview”视图只有每个可能组合的一行。 - Eugen Rieck

哇，距离我发表原始评论已经过去了八年多！嗯，我仍然认为这个查询不会起作用。你测试过吗？ - Dawood ibn Kareem

我经常使用这样的查询，而且我非常确定我在8年前测试过它。显然，我现在已经不知道了。重要的部分是distinct。 - Eugen Rieck

不，distinct 不能帮助你。这里有两个问题。(1) 子查询中没有 cityandcountry。(2) 没有与 count(*) 配合使用的 group by。当你在没有 group by 的情况下使用 count(*) 时，它会计算整个查询中的所有行。这根本不是 OP 想要的。 - Dawood ibn Kareem