CODEWITHSUNDEEP
mysql
Nheb
Nheb

Reputation: 11

MySQL: calculate percent in subgroup

I have a list of names with country and city.

| COUNTRY | CITY | NAME | ID|

I need to find a count of each name grouped by country and city. I also need percentage of each name WITHIN each city/country. For example:

RESULTS 

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

How do I calculate percentage of each name WITHIN each city/country?

Upvotes: 1

Views: 209

Answers (1)

Mike Dinescu
Mike Dinescu

Reputation: 55730

It looks like your query doesn't compute the count of each name grouped by city and state but rather the count of each id grouped by name, city and country. Assuming this is what you wanted, then the percents might be calculated like this:

 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

Here's a test fiddle I've created for it: http://sqlfiddle.com/#!2/875026/1/0

For the second question - from the comments,

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

You might do something like this:

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

Upvotes: 1

Related Questions