添加2个或更多不同行的列值。 SQL

Question

I have a table with the following scheme: http://i.stack.imgur.com/zH2zz.png
This table is for a hockey league (Link Here). This is the second season where stats will be held in a database. If you look select the dropdowns in the link, you will see that I can produce a top 20 lists for both seasons (1 season has no data yet other than 1 tester). The goal is to total the two seasons and have a All Time top 20 list. I have tried using a sum function, but that literally added every single row into 1.

I only want to sum the rows of repeated player id's. For example, I am player id #2 and I need every goal and assist added per row containing my PID, but I need this for all the top 20 players in 1 query. Here are some of the queries I am using thus far.

SELECT Rosters.PID, Rosters.Goals, Rosters.Assists, Rosters.PIM, Rosters.Num, Rosters.TID, Players.pid, Players.firstname, Players.lastname,
      (Rosters.Goals + Rosters.Assists) AS Points
      FROM Rosters
      INNER JOIN Players
      ON Rosters.PID = Players.pid
      WHERE Rosters.TID BETWEEN $parameter
      ORDER BY Points DESC, Goals DESC
      LIMIT 0,20

The $parameter is based on the team id (TID) There are 16 tid's (8 for each season). This query gets all of the records when the parameter is 1-16, it just doesn't add up non distinct rows by PID (This is ultimately what I would like to learn to do). This is the drop down showing how the query is being instantiated

<div class="ddstyle">
    <select name="DropDownTeams" id="DDTeams">
        <option>Select a Season</option>
        <option value="stats2.php?tid=9 AND 16">2015-2016</option>
        <option value="stats2.php?tid=1 AND 8">2014-2015</option>
        <option value="stats2.php?tid=1 AND 16">All Time</option>
    </select>
</div>

I tried to do:

SELECT Rosters.PID, SUM( Rosters.Goals ) Goals, SUM( Rosters.Assists ) Assists, SUM( Rosters.PIM ) PIM, Rosters.Num, Rosters.TID, Players.pid, Players.firstname, Players.lastname, 
    SUM((Rosters.Goals + Rosters.Assists)) AS Points
    FROM Rosters
    INNER JOIN Players
    ON Rosters.PID = Players.pid
    WHERE Rosters.TID BETWEEN 1 AND 16
    ORDER BY Points DESC, Goals DESC
    LIMIT 0,20;

But that just added up every record into a single row. The Where clause obviously is not working the way I want when used with the SUM() functions.

Answer 1

Include the Group BY section http://www.w3schools.com/sql/sql_groupby.asp

SELECT Rosters.PID
    ,SUM(Rosters.Goals) Goals
    ,SUM(Rosters.Assists) Assists
    ,SUM(Rosters.PIM) PIM
    ,Players.pid
    ,SUM((Rosters.Goals + Rosters.Assists)) AS Points
FROM Rosters
INNER JOIN Players ON Rosters.PID = Players.pid
WHERE Rosters.TID BETWEEN 1
        AND 16
GROUP BY 
    Players.pid
ORDER BY Points DESC
    ,Goals DESC 
LIMIT 0
    ,20;