mercredi 21 octobre 2015

SQL Query that summarizes students marks in a report card

Stupid simple question that I ended up spending 3.5 hours on. I'm running into a lot of syntax errors, so if anyone could help me answer this, I would learn a lot! Thank you so much!

I have 3 database tables:

Students Table
student_id, name
1, joe
2, jill

Courses Table
course_id, course_name
eng123, Engineering
stat111, Statistics

Marks Table
student_id, course_id, mark
1, stat111, 64
2, stat111, 90
1, eng123, 86

I need to write a single SQL query that will give me a summed up report card that looks like this:

student_id, student_name, eng123, stat 111
1, joe, 86, 64
2, jill, null, 90

---WHAT I EXPLORED: I have looked into PIVOT, CASE and GROUP BY as my main leads, but I cannot put together the final pieces. My most promising query so far has been:

SELECT Students.student_id, Students.student_name, 
        CASE course_id WHEN 'eng123' THEN mark END as 'eng123',
        CASE course_id WHEN 'stat111' THEN mark END as 'stat111'
      FROM Students
      INNER JOIN Marks
      ON Students.student_id=Marks.student_id;

But that gives me the incorrect results of:

student_id, student_name, eng123, stat111
1, joe, null, 64
1, joe, 86, null
2, jill, null, 90

Aucun commentaire:

Enregistrer un commentaire