CODEWITHSUNDEEP
sqlms-access
Tamas Karpati
Tamas Karpati

Reputation: 85

How do I get the sum of values from multiple columns in SQL

I have the following table:

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

Season indicates a soccer season, name1, name2 indicates a players position in a given game Goal1 indicates the number of goals Name1 scored

I would like to generate a list for each name per season how many times they played and the # of goals they scored. Something like this:

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

Any help would be appreciated!

Upvotes: 0

Views: 2510

Answers (3)

HansUp
HansUp

Reputation: 97101

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;

Notice you must use UNION ALL in that subquery. If you use just UNION instead, the subquery result set would include only one row for each combination of Season, player and goals. But when a player scores the same number of goals in more than one game during a season, you want to preserve each of those rows in order to allow an accurate count of games played and total goals scored.

Using your sample data in Access 2007, that query produces this result set.

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

Upvotes: 2

mbrowne81
mbrowne81

Reputation: 26

That's really convoluted, but you could write an in-line query that puts everything into a proper table format so then you can do your usual aggregations and grouping. Keep adding unions for 1 through x, depending on how many columns there are.

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

Upvotes: 0

Lamak
Lamak

Reputation: 70638

Ok, well, since you aren't sharing what RDBMS you are using, I think that this (ugly) query would work on most of them:

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

Disclaimer: It is an ugly query.

Upvotes: 1

Related Questions