How to calculate final grades for students enrolled in both Math and Art courses?

Courses
1

Hey everyone! I’m trying to figure out how to modify my SQL query. Right now, it calculates the final grade for all students in the Math course. But I want to tweak it so it only shows the final Math grade for students who are taking both Math and Art. Here’s what I’ve got so far:

SELECT student.id, 
       SUM(grades.score * assignments.weight / 100) AS final_grade
FROM student
JOIN grades ON student.id = grades.student_id
JOIN assignments ON grades.assignment_id = assignments.id
WHERE assignments.subject = 'Mathematics'
GROUP BY student.id

This gives me the Math grades, but how do I filter it to only show students enrolled in both subjects? Any help would be awesome! Thanks in advance!

2

Hey Luke87! I’ve been wrestling with similar queries lately, and I think I’ve got a neat trick that might help you out.

Have you considered using a Common Table Expression (CTE)? It could make your query a bit cleaner and potentially easier to understand. Here’s what I’m thinking:

WITH enrolled_students AS (
    SELECT student_id
    FROM enrollments
    WHERE course_name IN ('Mathematics', 'Art')
    GROUP BY student_id
    HAVING COUNT(DISTINCT course_name) = 2
)
SELECT s.id, 
       SUM(g.score * a.weight / 100) AS final_grade
FROM student s
JOIN grades g ON s.id = g.student_id
JOIN assignments a ON g.assignment_id = a.id
JOIN enrolled_students es ON s.id = es.student_id
WHERE a.subject = 'Mathematics'
GROUP BY s.id

This approach first creates a temporary result set with students enrolled in both Math and Art, then joins it with your original query. It might be a bit easier to read and maintain.

What do you think? Have you worked with CTEs before? They can be pretty handy for breaking down complex queries into more manageable chunks.

Oh, and just curious - are you planning to do something similar for the Art grades too? It might be interesting to compare the performance of students in both subjects!

3

I’ve dealt with a similar issue before. Here’s a solution that should work for you:

SELECT s.id, 
       SUM(g.score * a.weight / 100) AS final_grade
FROM student s
JOIN grades g ON s.id = g.student_id
JOIN assignments a ON g.assignment_id = a.id
JOIN enrollments e ON s.id = e.student_id
WHERE a.subject = 'Mathematics'
AND EXISTS (
    SELECT 1 FROM enrollments e2
    WHERE e2.student_id = s.id AND e2.course_name = 'Art'
)
AND e.course_name = 'Mathematics'
GROUP BY s.id

This query uses an EXISTS clause to check if the student is also enrolled in Art. It’s generally more efficient than using IN with a subquery for larger datasets. Make sure you have an index on enrollments(student_id, course_name) for optimal performance. Let me know if you need any clarification!

4

hey luke, i think i got a solution for ya. try adding this to ur query:

AND student.id IN (
SELECT student_id
FROM enrollments
WHERE course_name IN (‘Mathematics’, ‘Art’)
GROUP BY student_id
HAVING COUNT(DISTINCT course_name) = 2
)

this should filter for students takin both math n art. lmk if it works!