CODEWITHSUNDEEP
mysqlsql
Josh
Josh

Reputation: 424

MYSQL View and summing fields

I need some help I have been scouring the web and haven't been able to find something too similar. I have a MYSQL database for my Golf League. I need to display standings by creating a view from this database. There are 2 people per team, my primary key in 'players' is 'id' there is also a teamID (numerical value 1 - 20, 20 teams) for each player which corresponds to their teammates. Basically what I need is a view that contains 'teamID', both players 'LName' (maybe an 'LNameA','LNameB'), and a sum of the two players 'points' field. I have never summed a field from one person and another or created a view in MYSQL.

EDIT:

I was trying something like 

CREATE 
VIEW standings1
AS SELECT teamID, LName, points
FROM players

but need teamID to be the primaryKey of the view which will contain each players last name, and their points summed together.

Upvotes: 2

Views: 3590

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

create view standings as
  select teamId, group_concat(lname separator ', ') as TeamMembers,
    sum(points) TotalPoints from players
  group by teamId

Oh, one more thing. If you want to have the names of the players in different fields (group_concat just separate them by commas, but it is still a single field) you can use this query:

create view standings as
  select a.teamId, a.lname as player1, b.lname as player2,
  a.points + b.points TotalPoints
  from players a
  join players b ON a.teamId = b.teamId AND a.id >= b.id
  group by a.teamId, a.id
  having count(*) = 2

That way you can play better with the names in PHP without having to parse the ", "

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270607

If I understand your table structure, you will need a JOIN against the table's own teamID. I'm assuming the teamID refers to a team, and is not the id of the player. The trick here is to join two copies of the table on the same teamID, but where the player ids are non-equal. That should produce the pair of players per team.

CREATE VIEW standings AS
(
SELECT
  p1.teamID AS teamID,
  p1.id AS p1id,
  p2.id AS p2id,
  p1.LName AS p1LName,
  p2.LName AS p2LName,
  p1.score + p2.score AS totalScore
FROM
  /* JOIN on matching teamID and non-matching player-id (so you don't get the same player twice) */
  players p1 JOIN players p2 ON p1.teamID = p2.teamID and p1.id <> p2.id
);

Upvotes: 1

Related Questions