如何添加两个被联合在一起的查询

Question

我试图通过人一起添加两个组，这是我的代码：

SELECT VISITOR,COUNT(VISITOR) 
FROM ENGLAND 
WHERE TIER = 1 AND SEASON >= 1980 
GROUP BY VISITOR 
UNION 
SELECT HOME,COUNT(HOME) 
FROM ENGLAND 
WHERE TIER = 1 AND SEASON >= 1980 
GROUP BY HOME; 

这里是输出的一部分

AFC Bournemouth 37 
AFC Bournemouth 38 

和问题是我怎么能够使输出看起来像

AFC Bournemouth 75 

（这意味着增加游戏的国内和访问者NUM在一起）

Answer 1

计算机科学中的所有问题都可以通过另一个间接层面来解决。
- David Wheeler

您想同时添加相同名称的值吗？只要使用SUM（）和GROUP BY：

SELECT Name, SUM(Games) AS Games 
FROM (SELECT Visitor AS Name, COUNT(*) AS Games 
     FROM England 
     WHERE Tier = 1 AND Season >= 1980 
     GROUP BY Visitor 
     UNION ALL 
     SELECT Home, COUNT(*) 
     FROM England 
     WHERE Tier = 1 AND Season >= 1980 
     GROUP BY Home) 
GROUP BY Name; 

（UNION没有ALL会删除重复的，如果有相同数量的本地和访问者游戏可能出现这种情况）

---

另外，带原始表的形式，让他们都来一次汇总值：

SELECT Name, COUNT(*) AS Games 
FROM (SELECT Visitor AS Name 
     FROM England 
     WHERE Tier >= 1 AND Season >= 1980 
     UNION ALL 
     SELECT Home 
     FROM England 
     WHERE Tier >= 1 AND Season >= 1980) 
GROUP BY Name;