I'm trying to make a list of students and their efficiency with tests.
In MySql database I have tables
users - table with students
id | name
_________
1 | Joe
2 | Marry
3 | Max
4 | Anna
----------
courses - table with courses
id | name
_____________
1 | Course 1
2 | Course 2
----------
questions - table with questions for each course. Row cours_id indicates in which course that question belongs to
id | cours_id | question
_________________________________
1 | 1 | Course 1 - question 1
2 | 1 | Course 1 - question 2
3 | 1 | Course 1 - question 3
4 | 1 | Course 1 - question 4
5 | 2 | Course 2 - question 1
6 | 2 | Course 2 - question 2
7 | 2 | Course 2 - question 3
8 | 2 | Course 2 - question 4
cours_invitations - every student gets invitation for a course. Row user_id shows the id of user who is invited to do a course.
cours_id represents the id of the course which a student should do.
When row status has value 0 that means that a student didn't started with the course (pending), and if it has value 1 that means that the student has started it (or done).
id | user_id | cours_id | status
________________________________
1 | 1 | 1 | 1
2 | 1 | 2 | 0
3 | 2 | 1 | 0
4 | 3 | 1 | 1
5 | 4 | 1 | 1
6 | 4 | 2 | 1
Example: Joe and Anna are invited to do Course 1 and Course 2, Marry and Max are invited to do only Course 1. Joe did Course 1 but not Course 2, Marry didn't do anything and Max did Course 1
courses_stats - are the statistics of the courses' questions which students have done. Status represent the accuracy of the answer. 0 stands for wrong answer and 1 for correct.
id | user_id | question_id | status
___________________________________
1 | 1 | 1 | 1
2 | 1 | 2 | 1
3 | 1 | 3 | 0
4 | 2 | 1 | 1
5 | 2 | 2 | 1
6 | 2 | 3 | 1
7 | 2 | 4 | 1
8 | 4 | 1 | 1
9 | 4 | 2 | 1
10 | 4 | 3 | 0
11 | 4 | 4 | 0
12 | 4 | 5 | 1
13 | 4 | 6 | 1
Example: Joe did 3 questions from the first course.Notice that he didn't do all the questions from that course and that one is incorrect.
Max did all the questions correct and Anna did all question from first course (half are incorrect) and half from second course (all correct)
I need a query with sudents names, perecentage of finished courses, percentage of correct answers of those courses which they did (not all courses) and possibility to order students by those percentages. Something like this:
name | completed courses | completed questions
______________________________________________
Max |100% |100%
Anna |100% |50%
Joe |50% |50%
Marry |0% |0%
Is something like this even possible? Do I need more rows in tables for this query?
I think this should be what you need:
SELECT
users.name,
CONCAT(COUNT(
DISTINCT CASE
WHEN cours_invitations.status = 1 THEN
cours_invitations.id
ELSE
NULL
END
) / COUNT(
DISTINCT cours_invitations.id
) * 100, '%') AS completed_courses,
CONCAT(COUNT(
DISTINCT CASE
WHEN courses_stats.status = 1 THEN
courses_stats.id
ELSE
NULL
END
) / COUNT(DISTINCT questions.id) * 100, '%') AS completed_questions
FROM
users
LEFT JOIN cours_invitations ON cours_invitations.user_id = users.id
LEFT JOIN questions ON cours_invitations.cours_id = questions.cours_id
AND cours_invitations.status = 1
LEFT JOIN courses_stats ON users.id = courses_stats.user_id
GROUP BY
users.id
ORDER BY
completed_courses DESC,
completed_questions DESC
As a question back to you, why are the table names called cours_* rather than course_*?