PostgreSQL - JOIN on string_agg

Question

I have three tables.

Students
student_id | name
1            Rhon

Subjects
subject_id | subject_name | student_id
1            Physics        1
2            Math           1

Grades
grade_id | student_id | subject_id | grade
1          1            1            90
2          1            2            89
3          1            2            88

I would like to result to be like this:

student_id | student_name | subject_name | grades
1            Rhon           Physics        90
1            Rhon           Math           88,89

My current query is:

SELECT students.student_id, subjects.subject_id, string_agg(grades.grade, ',')
FROM students
JOIN subjects ON students.student_id = subjects.student_id
JOIN grades ON subjects.subject_id = grades.subject_id;

Is there something wrong with my query? Am I missing something? The error says that student_id needs to be in a GROUP BY clause but I don't want that.

Patrick · Accepted Answer · 2015-10-16 07:13:08Z

5

You can do this with a sub-query:

SELECT s.student_id, s.student_name, j.subject_name, g.grades
FROM students s
JOIN subjects j
JOIN (
  SELECT student_id, subject_id, string_agg(grade, ',') AS grades
  FROM grades
  GROUP BY student_id, subject_id) g USING (student_id, subject_id);

Why exactly do you not want to GROUP BY student_id?

edited Oct 16, 2015 at 7:13

answered Oct 16, 2015 at 3:50

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Ryan Camatog Over a year ago

Sorry about that. Forgot that I also get other columns for the said tables. I'm doing a report, info stated is different from the actual but still the same structure.

John Ryan Camatog Over a year ago

Works as expected. Thank you very much, good sir. I still can't upvote though.

Collectives™ on Stack Overflow

PostgreSQL - JOIN on string_agg

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related