MYSQL SUM and substract

Question

I have the following SQL query.

SELECT    u.username, SUM(p.points) AS points,
          SUM(sp.spPoints) AS spPoints,
          (SUM(sp.spPoints) - SUM(p.points)) AS Puntos_Restantes
FROM      users as u
LEFT JOIN points as p ON (u.userid = p.userid)
LEFT JOIN sppoints AS sp ON (u.userid = sp.userid)
WHERE     u.userid = '1'
GROUP BY  u.userid

My goal is to SUM 2 fields and then subtract them but when I execute the above query, the second SUM is wrong.
The tables are like this:

points: pointId, userId, points    
sppoints: spPointId, userId, spPoints    

In points I have this amount: 25 and in spPoints: 10 but when I run the query I get :

points  spPoints    Puntos_Restantes
 25       30              5

What is going wrong here?

Answer 1

The table users has a one-to-many relationship to both the other 2 tables. This causes the 2 joins to produce a mini-Carstesian product and multiple rows with same data in the points columns - which are then aggregated.

You can use subqueries to group by, and then join, to avoid this problem:

SELECT 
      u.username
    , COALESCE(p.pPoints,0) 
        AS pPoints
    , COALESCE(sp.spPoints,0) 
        AS spPoints
    , COALESCE(p.pPoints,0) - COALESCE(sp.spPoints,0)
        AS Puntos_Restantes
FROM 
      users as u
  LEFT JOIN 
      ( SELECT userid, SUM(points) AS pPoints
        FROM points
        WHERE userid = 1
        GROUP BY userid
      ) AS p
    ON u.userid = p.userid
  LEFT JOIN 
      ( SELECT userid, SUM(spPoints) AS spPoints
        FROM sppoints
        WHERE userid = 1
        GROUP BY userid
      ) AS sp
    ON u.userid = sp.userid
WHERE u.userid = 1 ;

If you want to have results for more than one user (or for all users), replace the three WHERE userid=1 conditions (or remove them altogether).

Indices on points(userid, points) and sppoints(userid, spPoints) will help with efficiency.