如何获得来自多个列的值的总和SQL

Question

我有以下表格：

Season Name1 Goal1 Name2 Goal2 Name3 Goal3 
1990 Smith 2  Abel 1  John 3 
1990 Smith 1  Abel 1  John 1 
1990 John 0  Smith 2  Abel 5 
1991 John 1  Smith 1  Abel 2 
1991 Abel 2  John 0  Smith 0 
1992 Abel 3  John 0  Smith 1 

季表示足球赛季，1，名称显示在一个给定的游戏玩家位置 第1个目标指示进球数量名字1得分

我想为每个赛季的每个名字生成一个列表，他们出场的次数和他们得分的目标数量。这样的事情：

Abel 1990 3 games played 7 goals scored 
Abel 1991 2 games played 4 goals scored 
Abel 1992 1 games played 3 goals scored 
John 1990 3 games played 2 goals scored 

任何帮助，将不胜感激！

Answer 1

好了，好了，因为你没有分享您所使用的RDBMS，我认为这（丑陋的）查询将其中的大部分工作：

SELECT Name + ' ' + CAST(Season AS VARCHAR(4)) + ' ' + 
     CAST(Games AS VARCHAR(4)) + ' games played ' + 
     CAST(Goals AS VARCHAR(4)) + ' goals scored' AS YourColumn 
FROM ( SELECT Season, Name, SUM(Goals) AS Goals, COUNT(*) AS Games 
     FROM ( SELECT Season, Name1 AS Name, Goal1 AS Goals 
       FROM YourTable 
       UNION ALL 
       SELECT Season, Name2 AS Name, Goal2 AS Goals 
       FROM YourTable 
       UNION ALL 
       SELECT Season, Name3 AS Name, Goal3 AS Goals 
       FROM YourTable) AS A 
     GROUP BY Season, Name) X 

免责声明：这是一个丑陋的查询。

Answer 2

这实在令人费解，但您可以编写一个内嵌查询，将所有内容放入适当的表格中，以便您可以进行常规聚合和分组。继续添加1到x的联合，具体取决于有多少列。

SELECT Season, Name, Sum(Goals) 
FROM (SELECT Season, Name1 as Name, Goals1 as Goals   
     FROM table 
     UNION 
     SELECT Season, Name2 as Name, Goals2 as Goals   
     FROM table 
     UNION 
     SELECT Season, Name3 as Name, Goals3 as Goals   
     FROM table) newtable 
GROUP BY Season, Name 

---

Answer 3

SELECT 
    sub.player, 
    sub.Season, 
    Count(*) AS games_played, 
    Sum(sub.goals) AS SumOfgoals 
FROM 
    (
     SELECT Season, Name1 AS player, Goal1 AS goals 
     FROM YourTable 
     UNION ALL 
     SELECT Season, Name2, Goal2 
     FROM YourTable 
     UNION ALL 
     SELECT Season, Name3, Goal3 
     FROM YourTable 
    ) AS sub 
GROUP BY sub.player, sub.Season 
ORDER BY sub.player, sub.Season; 

通知您必须使用UNION ALL的子查询。如果您仅使用UNION代替，则子查询结果集将包含季节,播放器和目标的每个组合仅包含一行。但是，当一名球员在一个赛季中在多场比赛中获得相同数量的进球数时，您希望保留这些排中的每一排，以便准确计数所打的比赛数和总进球数。

在Access 2007中使用您的示例数据，该查询生成此结果集。

player Season games_played SumOfgoals 
Abel  1990   3   7 
Abel  1991   2   4 
Abel  1992   1   3 
John  1990   3   4 
John  1991   2   1 
John  1992   1   0 
Smith 1990   3   5 
Smith 1991   2   1 
Smith 1992   1   1