Compter le nombre d'enregistrements dans un sous-groupe tout en conservant un nombre d'enregistrements dans un groupe.

Question

J'ai un tableau avec la structure suivante (c'est une version simplifiée, juste pour montrer l'idée) :

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

Je peux utiliser group by pour obtenir le nombre total de personnes dans chaque ville, comme ça :

select count(*) from my_table group by city

Mais j'ai besoin d'un peu plus et je n'arrive pas à m'y retrouver : J'ai besoin d'obtenir un nombre de toutes les personnes ayant le même nom dans la même ville tout en gardant un nombre total de personnes dans cette ville. Voici à quoi devrait ressembler le résultat :

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

Je sais que je peux prendre des données brutes de la base de données et faire des calculs dans mon programme Java, mais ce serait génial de le faire en SQL simple.

Mise à jour : J'utilise mysql et je ne peux pas utiliser over clause.

Answer 1

select  distinct name
,       count(*) over (partition by Name) as TotalWithThisName
,       count(*) over (partition by City) as TotalInThisCity
,       city
from    YourTable

Answer 2

La solution que j'ai trouvée jusqu'à présent est d'utiliser une sous-requête avec join . Ça ressemble à ça :

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;