Count number of records in subgroup while keeping a number of records in a group

I have a table with the following structure (it’s a simplified version, just to show the idea):

name    |  city
------------------
John    | New York
Thomas  | Berlin
Hans    | Berlin
Boris   | Moscow
Boris   | Moscow
Vasiliy | Moscow

I can use group by to get a total number of people in every city, like so:

select count(*) from my_table group by city

But I need a little bit more and I can’ wrap my head around it: I need to get a number of all people with the same name in the same city while keeping a total number of people in that city. This is how the result should look like:

name    | totalWithThisName | totalInThisCity | city
--------------------------------------------------------
John    |         1         |        1        | New York
Thomas  |         1         |        2        | Berlin
Hans    |         1         |        2        | Berlin
Boris   |         2         |        3        | Moscow
Vasiliy |         1         |        3        | Moscow

I know that I can take a raw data from db, and make calculations in my java program, but it would be great to make it in a plain SQL.

Update: I’m using mysql and I can’t use over clause.

Answer

The solution I’ve made so far is to use subquery with join. It looks like this:

select
    name,
    city,  
    count(*) as totalWithThisName,
    T.totalInThisCity
from 
    my_table 
    join (select
              count(*) as totalInThisCity,
              city
          from
              my_table
          group by city) T on my_table.city = T.city
group by 
    city, name;

Advertisement

Answer