MySQL concat UNION GROUP BY 行结果

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