How to avoid counting the pending statuses of the enrolled students?

Question

I have the following query where I am bringing all the information that is linked to the author of the course, all the rest works very well, except when counting the enrolled students COUNT (i.id_inscribed) since a condition has passed to the WHERE i.status = 'Completed' where it should only take into account the students that have the completed status.

$stmt = $con->prepare("SELECT AVG(ra.rating) AS avg_rating,
                            COUNT(ra.rating) AS valuations,
                            SUM(c.study_program='intensive') AS total_intensive,
                            SUM(c.study_program='proyect') AS total_proyect,
                            COUNT(i.id_inscribed) AS enrolled_i
                        FROM tbl_ratings ra
                            LEFT JOIN tbl_courses c ON ra.id_course = c.id_course
                            LEFT JOIN tbl_author a ON c.id_author = a.id_author
                            LEFT JOIN tbl_inscribed i ON i.id_course = c.id_course
                        WHERE
                            a.id_author=1 AND
                            i.status='Completed' AND
                            (c.study_program='intensive' OR
                            c.study_program='proyect')
                        GROUP BY a.id_author;");

Here the query and the data obtained in real time:

https://www.db-fiddle.com/f/s2uBnyTLgTXqADtVhkgEUv/2

You will notice that in the enrolled_i it shows 2 and, it should show 1 because of the type of condition passed in the WHERE. What am I doing wrong?

There is information that has nothing to do with the author of the course, the idea of this query is to obtain the total of reviews that that instructor has had and, the general evaluation of the instructor for all the votes of his courses, the total of courses of type intensive course that the instructor and project-type courses have. Now the total number of students that the instructor has is here where you only want to obtain the data with the completed status.

And, when doing this:

(c.study_program='intensive' OR c.study_program='proyect')

I'm getting bad data, to the aforementioned approach:

avg_rating  valuations  total_intensive total_proyect   enrolled_i
    5            1              1             0              1

The expected output is the following:

avg_rating  valuations  total_intensive total_proyect   enrolled_i
    4            2              1             1              1

The changes mentioned, which generate an error for me here: https://www.db-fiddle.com/f/s2uBnyTLgTXqADtVhkgEUv/3

This part of the conditions: `c.study_program='intensive' OR c.study_program='proyect'` must be enclosed inside parentheses. — forpas, Jun 05 '21 at 15:28
@forpas If I close it, it doesn't tell me this `SUM (c.study_program = 'project') AS total_proyect` https://www.db-fiddle.com/f/s2uBnyTLgTXqADtVhkgEUv/3 — , Jun 05 '21 at 15:35
You get 0 for total_proyect because there is no c.study_program = 'project' with i.status='Completed'. — forpas, Jun 05 '21 at 15:41
@forpas No, but the approach is wrong, one thing is to obtain the total number of registered students and another thing is to count the total number of courses that the instructor has and, that is very different if the student is registered or not, as well as these data : `avg_rating (5) valuations (1)` and, you should get this data: `avg_rating (4) valuations (2)` — , Jun 05 '21 at 15:45
@forpas In conclusion this is the expected output: `avg_rating(4) valuations(2) total_intensive(1) total_proyect(1) enrolled_i(1)` — , Jun 05 '21 at 15:46
Edit your question and explain what you want and what your expected output is. As it is your question it mentions only the problem of counting enrolled_i. — forpas, Jun 05 '21 at 15:48
@forpas I already edited it, it is that at first glance that was only the error I had, but now with the changes mentioned in your comment it already generates the data wrong. — , Jun 05 '21 at 15:55
What a bad vibe for the negative votes, they link me to a similar question, but the supposed solution to the one that closes my question, generates wrong data to what I already had: / — , Jun 05 '21 at 16:02

score 0 · Accepted Answer · answered Jun 05 '21 at 16:04

The main problem in your code is that you start the joins from tbl_ratings and then LEFT join all the other tables.
The joins should start from tbl_author.
Also, all the conditions in the WHERE clause, except the one about the author should be moved to the ON clauses of the joins, becuse as they are, they change all the joins to INNER joins:

SELECT AVG(ra.rating) AS avg_rating,
       COUNT(ra.rating) AS valuations,
       SUM(c.study_program='intensive') AS total_intensive,
       SUM(c.study_program='proyect') AS total_proyect,
       COUNT(i.id_inscribed) AS enrolled_i
FROM tbl_author a
LEFT JOIN tbl_courses c ON c.id_author = a.id_author AND (c.study_program='intensive' OR c.study_program='proyect')
LEFT JOIN tbl_ratings ra ON ra.id_course = c.id_course
LEFT JOIN tbl_inscribed i ON i.id_course = c.id_course AND i.status='Completed'
WHERE a.id_author=1

See the demo.

How to avoid counting the pending statuses of the enrolled students?

1 Answers1