1

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
5

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 ;
  • If I understand correctly, SUM() aggregates over all rows with identical values of those columns that appear in the GROUP BY clause. Here score1 & score2 are aggregated independently and then the 3 values of score1_agg, score2_agg and bonus are added for each row. – user4150760 Jun 24 '15 at 14:38
  • Yes, that's right. – ypercubeᵀᴹ Jun 24 '15 at 14:39
  • Is there a difference between sum(score1+score2) vs sum(score1)+sum(score2) ? – user4150760 Jun 24 '15 at 14:40
  • 2
    Not really any difference (unless there are nulls). – ypercubeᵀᴹ Jun 24 '15 at 14:40
1

You need to group by sudjects or otherwise you get the sum multiplicated each time you add one more sudject:

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,sc.subject

Hope it helps.

  • I don't think they want sum per subject. But sum per student. Plus, this won't solve the issue with bonus. – ypercubeᵀᴹ Jun 24 '15 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.