SQL Query that summarizes students marks in a report card

Question

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

the result is correct. the problem is more that you need to have things in columns instead of lines as is usual for sql (it defaultwise puts results into 1 line per result found instead of 1 part row there). mainquestion is though is the number of courses dynamic? (I take it so) or is there a total maximum to them? — Thomas, Oct 21 '15 at 08:36
example for getting a column instead of row result: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — Thomas, Oct 21 '15 at 08:37
Decide which dbms you are using, MySQL and SQL Server are different products. In ANSL SQL single quotes are for string literals, use double quotes for identifiers, e.g. column alias "eng123". — jarlh, Oct 21 '15 at 08:43
I've submitted an edit to remove the `MySQL` and `SQLite` tags as the use of `pivot` clearly indicates this is T-SQL. Good point re identifiers. — underscore_d, Oct 21 '15 at 09:07

Tim Biegeleisen · Answer 1 · 2015-10-21T08:45:05.860

1

Your promising query is a stone's throw away from being what you want. You can simply modify it to GROUP BY the student_id and student_name (which should always pair the same together). Then take the sum of the marks. Note that I have added ELSE conditions to your CASE statements which assign a value of 0 (which therefore won't affect the sum aggregate).

SELECT Students.student_id, Students.student_name, 
    SUM(CASE course_id WHEN 'eng123' THEN mark ELSE 0 END) as 'eng123',
    SUM(CASE course_id WHEN 'stat111' THEN mark ELSE 0 END) as 'stat111'
FROM Students
INNER JOIN Marks ON Students.student_id=Marks.student_id
GROUP BY Students.student_id, Students.student_name

edited Oct 21 '15 at 08:45

answered Oct 21 '15 at 08:37

Tim Biegeleisen

502,043
27
286
360

the question is though if its ONLY those two courses or if there are also possible others (asked taht in the questions comments). If the later a case solution won't bring much – Thomas Oct 21 '15 at 08:40
I think it is safe to assume that a given student will have only one mark per course in the `Marks` table, unless the OP states otherwise (it doesn't). – Tim Biegeleisen Oct 21 '15 at 08:43
Yeah although I meant it in relation to the case as is. If its just the two courses the case does its work BUT if the number of courses are variable then a case where you put in 'eng',.... won't work. That is what I had meant there and why I asked the OP for clarification there if that is what he needs or if he needs a solution that also works for a variable amount of courses – Thomas Oct 21 '15 at 08:46
Hi Tim, thanks for the answer! It gave the desired output (although I swear I tried GROUP BY... i should have taken a break haha). Hi Thomas, what do you mean the case solution won't bring much? From what I understand, you are asking if there are more possible courses? (Which would mean a desirable SQL query needs to be scalable? Not involving the manual typing of 'eng123' and 'stat211'?) – Kon Huang Oct 21 '15 at 15:14
Please mark as correct if it solved your problem, thanks. – Tim Biegeleisen Oct 21 '15 at 15:19

anwaar_hell · Answer 2 · 2015-10-21T08:50:33.447

0

      select student_id,student_name,
max(case when course_id = 'eng123' then mark end) as eng123,
max(case when course_id = 'stat111' then mark end) as stat111
from (
SELECT Students.student_id as student_id,
 Students.name as student_name,
 Marks.course_id as course_id,
 Marks.mark as mark
      FROM Student Students
      INNER JOIN Marks
      ON Students.student_id=Marks.student_id)Z
      group by student_id,student_name

edited Oct 21 '15 at 08:50

answered Oct 21 '15 at 08:40

anwaar_hell

756
5
23

I fear you are missing a group by if you use max AND non summarizing columns in one and the same select statement – Thomas Oct 21 '15 at 08:41
@Thomas thanks for pointing it out..i hopes now the query will give expected results.. – anwaar_hell Oct 21 '15 at 08:51

score 0 · Answer 3 · answered Oct 21 '15 at 08:44

Hi try using aggregate function MAX. try this:

WITH x AS (SELECT 1 AS student_id, 'joe' AS student_NAME FROM dual UNION ALL
                   SELECT 2 AS student_id, 'jill' AS student_NAME FROM dual),
         y AS (SELECT 1 AS student_id , 'stat111' AS course_id, 64 AS mark FROM dual UNION ALL
                   SELECT 2 AS student_id , 'stat111' AS course_id, 90 AS mark FROM dual UNION ALL
                   SELECT 1 AS student_id , 'eng123' AS course_id, 86 AS mark FROM dual      )

SELECT x.student_id, x.student_name, 
       MAX (CASE WHEN course_id = 'eng123' THEN mark END)  eng123,
       max(CASE WHEN course_id = 'stat111' THEN mark END)  stat111
      FROM x
      INNER JOIN y
      ON x.student_id=y.student_id
      group by x.student_id, x.student_name;

SQL Query that summarizes students marks in a report card

3 Answers3