PostgreSQL – Avoiding SUM Over Same Values Multiple Times

aggregatepostgresqlsum

I've a student table and a table of scores in various subjects. I want to sum score1 & score2 for each student across all subjects and then add the bonus for each student. 

CREATE TABLE student (
   id serial PRIMARY KEY,
   name text NOT NULL,
   bonus integer NOT NULL,
);

CREATE TABLE score (
  id serial PRIMARY KEY,
  subject text NOT NULL,
  score1 integer NOT NULL,
  score2 integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id),
);

The query to join score1 & score2 looks like this: 

SELECT st.name, sum(sc.score1 + sc.score2) as total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
group by st.name

If I add bonus to this query i.e. sum(sc.score1 + sc.score2 + st.bonus), it gets repeated for each student multiple times (depending on how many times student_id occurs in score table).

Do I have to use a subquery i.e. compute the sum of score1 & score2 first and then add that to bonus (see below) or is there a better way?

SELECT sq.name, sum(sq.bonus+sq.total) FROM
( SELECT st.bonus, st.name, sum(sc.score1 + sc.score2) as total
  FROM student st
  LEFT JOIN score sc ON sc.student_id = st.id
  group by st.name
) AS sq

Best Answer

You could use a subquery but you don't need to. Just don't sum the bonus and add it in the GROUP BY list.
Notice that you have to also add the student.id, even in your original query, in case you have 2 students with same name.
You probably also need coalesce() for students without any scores:

SELECT st.name, 
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id, st.name, st.bonus ;

In newer versions of Postgres, you could use only the primary key of the student table in the group by:

SELECT st.name, 
       coalesce(sum(sc.score1),0) + coalesce(sum(sc.score2),0) + st.bonus AS total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
GROUP BY st.id ;

If you want a subquery, this is one way:

SELECT st.name, 
       coalesce(sc.score, 0) + st.bonus AS total
FROM student st
LEFT JOIN 
    ( SELECT student_id, sum(score1) + sum(score2) AS score
      FROM score 
      GROUP BY student_id
    ) AS sc ON sc.student_id = st.id ;
Related Question