Fill the table with data (Player Rankings)

Question

Hello i am have 3 tables:

1. players (id, name, surname)
2. games (id, id_gamer, points)
3. totals (id, id_gamer, name_gamer, total_Points, position)

In tables: players and games, records input by administrator,

in table totals I want through sql query input information about on each player (id_gamer and name_gamer), and count of points (sum (points)) and position in the rating

I start to do this but it doesn't work

INSERT INTO totals (id_gamer, name_gamer) SELECT id, Name FROM players ;

thanks

Answer 1

You can use this to fill in the total points for each player:

INSERT INTO totals (id_gamer, name_gamer, total_points)
SELECT p.id, p.name, IFNULL(SUM(g.points), 0)
FROM players AS p
LEFT JOIN games AS g ON p.id = g.id_gamer
GROUP BY p.id

Once you have the points, you can update it to fill in the positions. See Rank function in MySQL for ways to calculate rank in MySQL.