Get attempts of exams for every course of every student-CodePudding

I'm new to SQL. I want to get attempts of exams for every course of every student.

I've made this query in my database:

SELECT students.id, students.surname, students.name, courses.name AS "course" FROM `students`
JOIN exam_student
ON exam_student.student_id = students.id
JOIN exams
ON exam_student.exam_id = exams.id
JOIN courses
ON exams.course_id = courses.id 
ORDER BY students.surname ASC, students.name ASC;

The query produces this

Now, what I want is to get for every user, the attempts of exams of every course name.

I have the exams pivot with user_id and course_id.

How can I reach this result?

CodePudding user response：

Seems you want a total.
So let's COUNT the attempts by aggregating it.

SELECT s.id, s.surname, s.name
, c.name AS course
, COUNT(e.id) AS exam_attempts
FROM students s
LEFT JOIN exam_student es ON es.student_id = s.id
LEFT JOIN exams e ON e.id = es.exam_id
LEFT JOIN courses c ON c.id = e.course_id
GROUP BY s.id, s.surname, s.name, c.id, c.name
ORDER BY s.surname, s.name, c.name;