MySQL:计算子组中的百分比

Question

我有一个包含国家和城市的姓名列表。

| COUNTRY | CITY | NAME | ID|

我需要找到按国家和城市分组的每个名字的计数。我还需要每个城市/国家/地区中每个名称的百分比。例如:

结果

SELECT count(ID), country, city, name
from table NAME
GROUP BY country, city, name

如何计算每个城市/国家/地区中每个名称的百分比？

Answer 1

您的查询似乎没有计算按城市和州分组的每个名称的计数，而是计算按名称、城市和国家/地区分组的每个 ID 的计数。假设这是您想要的，那么百分比可以这样计算:

 SELECT count(N.ID) as NameCount
      , count(N.ID) * 100.0 / (SELECT COUNT(*) FROM NAME WHERE Country = N.Country AND City = N.City) as NamePercent 
      , N.Name
      , N.Country
      , N.City
 FROM NAME N
 GROUP BY N.country, N.city, N.Name

这是我为它创建的测试 fiddle :http://sqlfiddle.com/#!2/875026/1/0

对于第二个问题——来自评论，

How to calculate AVE of name occurance across different cities grouped by a country. For example: count of Mikes in NY-2, Chicago-4, LA-5. So ave for Mike in US is 3.6

你可能会这样做:

SELECT  Name
      , Country
      , AVG(NameCount) NameAvgAcrossCountry
FROM 
   (SELECT count(N.ID) as NameCount
         , count(N.ID) * 100.0 / (SELECT COUNT(*) FROM NAME WHERE Country = N.Country AND City = N.City) as NamePercent 
         , N.Name
         , N.Country
         , N.City
    FROM NAME N
    GROUP BY N.country, N.city, N.Name) NamesQuery
GROUP BY Name, Country