CODEWITHSUNDEEP
sqlsqlite
HellOfACode
HellOfACode

Reputation: 1743

SQLite select query summarizing values

I have two tables:

player[playerId, name, surname, age]
team[teamid, team_name, player1Id, player2Id, games_won, games_lost]

One player can be in multiple teams. How to create a SELECT query where the output would be:

-player name
-age
-games_won(summarized value from all teams where player has won)

Player table

 id | name | surname | age |
 1  | jake | wolf    | 23  |
 2  | drake| blake   | 22  |
 3  | luke | lake    | 25  |
 4  | adam | smith   | 23  |

Team table

 id | team_name | p1_Id | p2_Id | games_won | games_lost |
 1  | racers    | 1     | 3     |  6        |     2      |
 1  | traders   | 1     | 2     |  4        |     1      |
 1  | grands    | 1     | 4     |  1        |     2      |
 1  | unicorns  | 2     | 3     |  3        |     2      |

 SELECT p.name||" "||p.surname AS 'playerName', .p.age, t.games_won  FROM Player p INNER JOIN Team t ON p.id=t.p1_id

But this does not get the sumarized value of all won games by jake wolf who has played in 3 teams and has won 11 games total.

Upvotes: 0

Views: 79

Answers (2)

josepn
josepn

Reputation: 367

You can try this version:

SELECT name || ' ' || surname AS PlayerName,
       age,
       (SELECT SUM(games_won) FROM team WHERE player1Id = playerId OR player2Id = playerId) AS Total
FROM player
;

Test here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You have a very confusing data structure. If I presume that a team has two players and the team table summarizes them, then split it into two and do a join:

select p.name, p.surname, p.age,
       sum(games_won) as games_won, sum(games_lost) as games_lost 
from ((select player1id as playerid, games_won, games_lost
       from team
      ) union all
      (select player2id, games_won, games_lost
       from team
      )
     ) tp join
     player p
     on p.playerid = tp.playerid
group by p.name, p.surname, p.age;

A better data structure would be to have a junction/association table between teams and players. It would have two important columns:

  • TeamId
  • PlayerId

Upvotes: 1

Related Questions