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