title: MySQL concat UNION GROUP BY row results
body: <p>i have a query to return me the total of cities i have on my DB.</p>
  
  <p>Ok lets me introduce: one person can have his address, but he can also have the address associated to his work address. And i want to return te total of cities where the person are.</p>
  
  <p>Supposing i have </p>
  
  <pre><code>Pablo living in Lisbon but working in Porto
  Jim   living in Paris  and working in Paris
  May   living in Lisbon and working in Paris
  
  I have i should have a result like:  
  Paris  - 3  
  Lisbon - 2  
  Porto  - 1
  </code></pre>
  
  <p>After all, my query is:</p>
  
  <pre><code>SELECT  
    entity_address.city  as name, 
    entity_address.city as id, 
    COUNT(entity_address.city) as count_all
  FROM `entities` LEFT JOIN enterprises_entities ON entities.id = enterprises_entities.entity_id
   LEFT JOIN entities AS `enterprises` ON enterprises.id = enterprises_entities.enterprise_id
   LEFT JOIN addresses as enterprise_address ON enterprise_address.id = enterprises_entities.address_id
   LEFT JOIN addresses as entity_address ON entity_address.entity_id = entities.id
   LEFT JOIN person_titles ON person_titles.id = entities.title_id AND entities.title_type = 'PersonTitle'
   LEFT JOIN enterprise_activities ON enterprise_activities.id = enterprises.title_id AND enterprises.title_type = 'EnterpriseActivity'
   LEFT JOIN positions ON entities.position_id = positions.id 
  
  WHERE enterprise_address.city != '' OR entity_address.city != ''
  GROUP BY name
  
  UNION
  
  SELECT 
    enterprise_address.city as name,
    enterprise_address.city as id, 
    COUNT(enterprise_address.city) as count_all
  FROM `entities` LEFT JOIN enterprises_entities ON entities.id = enterprises_entities.entity_id
   LEFT JOIN entities AS `enterprises` ON enterprises.id = enterprises_entities.enterprise_id
   LEFT JOIN addresses as enterprise_address ON enterprise_address.id = enterprises_entities.address_id
   LEFT JOIN addresses as entity_address ON entity_address.entity_id = entities.id
   LEFT JOIN person_titles ON person_titles.id = entities.title_id AND entities.title_type = 'PersonTitle'
   LEFT JOIN enterprise_activities ON enterprise_activities.id = enterprises.title_id AND enterprises.title_type = 'EnterpriseActivity'
   LEFT JOIN positions ON entities.position_id = positions.id 
  
  WHERE enterprise_address.city != '' OR entity_address.city != ''
  GROUP BY name 
  
  order by count_all DESC 
  
  LIMIT 5
  </code></pre>
  
  <p>Ok the query is a little bit complex i understand. But my problem is the result it's not agrouped like i would:</p>
  
  <pre><code> +---------+---------+-----------+
   | name    | id      | count_all |
   +---------+---------+-----------+
   | Lisbon  | Lisbon  |      5100 |
   +---------+---------+-----------+
   | Lisbon  | Lisbon  |       932 |
   +---------+---------+-----------+
   | Paris   | Paris   |       430 |
   +---------+---------+-----------+
   | Porto   | Porto   |       270 |
   +---------+---------+-----------+
   | Paris   | Paris   |        92 |
   +---------+---------+-----------+
  </code></pre>
  
  <p>I would like to receive like:</p>
  
  <pre><code> +---------+---------+-----------+
   | name    | id      | count_all |
   +---------+---------+-----------+
   | Lisbon  | Lisbon  |      6032 |
   +---------+---------+-----------+
   | Paris   | Paris   |       512 |
   +---------+---------+-----------+
   | Porto   | Porto   |       270 |
   +---------+---------+-----------+
   | London  | London  |        80 |
   +---------+---------+-----------+
   | Berlin  | Berlin  |        10 |
   +---------+---------+-----------+
  </code></pre>
  
  <p>How can i write my query to perform me the desired values.
  Thank you!</p>