sql - SUM with multiple LEFT JOINS with VIEWS

Question

I need to SUM two columns (pts_won) from two different tables (prognosis_scores and question_scores) but it doesn't work properly. If there is many prognosis_scores or many question_scores the sum will be not accurate (For instance if there is one question_scores and three prognosis_scores the sum will add 3 times the pts_won of thee question_scores) :

Here the result of the sum will be 30 and i want it to be 10

| prognosis_scores | question_scores|
| ---------------- | -------------- |
| pts_won          | pts_won        |

Here is my code

    CREATE VIEW score_calculations
    AS SELECT 
    users.id as user_id,
    users.name as name,
    users.company_id as company_id,
    users.team_id as team_id,
    users.email_verified as email_verified,
    -- users.email as email,
    SUM(COALESCE(prognosis_scores."pts_won", 0) + COALESCE (question_scores."pts_won", 0) ) as pts_won,
    SUM(prognosis_scores."good_gap") as good_gap,
    SUM(prognosis_scores."good_score") as good_score,
    SUM(prognosis_scores."isGoodPrognosis"::INT) as good_winner
    FROM users
    
    LEFT JOIN prognosis_scores
    ON prognosis_scores.user_id=users.id
    LEFT JOIN question_scores
    ON question_scores.user_id=users.id
    GROUP BY users.id , users.name, users.company_id,team_id,email_verified;

how can i solve this with just sum both one time ?

Answer 1

In Postgres, this is probably simplest with a lateral join:

SELECT u.id as user_id, u.name, u.company_id, u.team_id, u.email_verified,
-- users.email as email,
   COALESCE(ps.pts_won, 0) + COALESCE (qs.pts_won, 0) ) as pts_won,
   ps.good_gap, ps.good_score, ps.good_winner
FROM users u LEFT JOIN LATERAL
     (SELECT SUM(ps."pts_won") as pts_won,
             SUM(ps.good_gap) as good_gap,
             SUM(ps.good_score) as good_score,
             SUM(ps."isGoodPrognosis"::INT) as good_winner
      FROM prognosis_scores ps
      WHERE ps.user_id = u.id
     ) ps
     ON 1=1 LEFT JOIN LATERAL
     (SELECT SUM(qs."pts_won") as pts_won
      FROM question_scores qs
      WHERE qs.user_id = u.id
     ) qs
     ON 1=1;

This lets you aggregate before doing the JOIN -- so the totals are accurate.

Notes:

• Table aliases make the query easier to write and to read.
• The default columns alias is the column name, so there is no need to write u.name as name. No harm if you like typing a lot, I suppose.
• Avoid double quotes on columns. You are stuck if the table is defined that way -- so you should fix the definition.