MySQL concat UNION GROUP BY row results

Question

我有一个查询返回我的城市总数我有我的数据库。

好的，让我来介绍一下:一个人可以有他的地址，但他也可以有与他的工作地址相关的地址。我想返回这个人所在城市的总数。

假设我有

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

毕竟我的查询是:

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

好吧，我知道这个查询有点复杂。但我的问题是结果它没有像我那样分组:

 +---------+---------+-----------+
 | name    | id      | count_all |
 +---------+---------+-----------+
 | Lisbon  | Lisbon  |      5100 |
 +---------+---------+-----------+
 | Lisbon  | Lisbon  |       932 |
 +---------+---------+-----------+
 | Paris   | Paris   |       430 |
 +---------+---------+-----------+
 | Porto   | Porto   |       270 |
 +---------+---------+-----------+
 | Paris   | Paris   |        92 |
 +---------+---------+-----------+

我希望收到这样的邮件:

 +---------+---------+-----------+
 | name    | id      | count_all |
 +---------+---------+-----------+
 | Lisbon  | Lisbon  |      6032 |
 +---------+---------+-----------+
 | Paris   | Paris   |       512 |
 +---------+---------+-----------+
 | Porto   | Porto   |       270 |
 +---------+---------+-----------+
 | London  | London  |        80 |
 +---------+---------+-----------+
 | Berlin  | Berlin  |        10 |
 +---------+---------+-----------+

我怎么写我的查询来执行我想要的值。谢谢你！

Answer 1

您只需要对这两个查询的聚合值进行SUM，即:

SELECT t.id, SUM(t.count_all)
FROM (
  SELECT entity_address.city as id, COUNT(entity_address.city) as count_all
  FROM /* rest of your first query */
  UNION
  SELECT enterprise_address.city as id, COUNT(enterprise_address.city) as count_all
  FROM /* rest of your second query */
) t
GROUP BY t.id