SQL sum two tables in one query

user_1075401 0
Viewed 90

I have users table with all the information about my users, with an id column. I also have 2 other tables with points each user got in a game they played. Lets call them game1 and game2.

db-fiddle

create table users (id text, username text);
create table game1 (user_id text, points int);
create table game2 (user_id text, points int);

A user can have more than one row in each table. I want to sum up all the points each user got.

Here's an example:

-- Users table
id      username
user1   Adam
user2   John
user3   Martha

-- game1 table
user_id points
user1   10
user2   9
user1   8
user1   7

--game2 table
user_id points
user2   1
user3   2
user1   3
user1   4

When I try to use two left joins I get duplicate values and nulls... obviously I'm doing something wrong here...

here's my sql:

-- try to sum all games points for each player
select u.id, u.username, sum(game1.points) + sum(game2.points) as sum_all_games_points
from users u
  LEFT JOIN game1 ON game1.user_id = u.id
  LEFT JOIN game2 ON game2.user_id = u.id
group by u.id, u.username;

The result for this query is:

id      username    sum_all_games_points
user2   John        10
user3   Martha      null
user1   Adam        71

The results that I'm expecting to get is this (No need for user id):

username    sum_all_games_points
John        32                -- This is the sum of (10 + 8 + 7 + 3 + 4)
Martha      2                 -- Only one game played on game2
Adam        10                -- 9 points from game1 and 1 point from game2
postgresql sql
2 Answers

You are getting strange results for a couple of reasons. First your JOINs are creating multiple rows for each user so some values are being double-counted. Second, you need to use COALESCE to convert NULL values to 0 to avoid getting a NULL output from the addition. This should work:

SELECT u.id, u.username, 
       COALESCE(g1.points, 0) + COALESCE(g2.points) AS sum_all_games_points
FROM users u
LEFT JOIN (SELECT user_id, SUM(points) AS points
           FROM game1
           GROUP BY user_id) g1 ON g1.user_id = u.id
LEFT JOIN (SELECT user_id, SUM(points) AS points
           FROM game2
           GROUP BY user_id) g2 ON g2.user_id = u.id

Output:

id      username    sum_all_games_points
user1   Adam        32
user2   John        10
user3   Martha      2

Demo on dbfiddle

user_94737640

You can try to union tables game1 and game2 and join them to initial table after that.

SELECT 
    U.ID, 
    U.USERNAME,
    COALESCE(GG.POINTS, 0) AS ALL_POINTS
FROM
    USERS U
LEFT JOIN            
    (SELECT 
        USER_ID, 
        SUM(POINTS) AS POINTS
    FROM           
        (SELECT * FROM GAME1
        UNION ALL
        SELECT * FROM GAME2) G
    GROUP BY 
        USER_ID) GG
ON 
    GG.USER_ID = U.ID
user_118655440
Related