Member Avatar for 54uydf

I want to make a query that would return data about students who failed in a specific course, the report is looking like this-
studId, studName, last grade, year last course was taken, num of fails in this course.
so I'm having problems writing the correct SQL query to get the year on which the student took the course last time, and the grade he got on that year.

table data looks like this-
studId, courseId, yearTaken, grade, pass/fail
1111, 1, 2008, 20, failed
1111, 1, 2009, 30, failed
1111, 1, 2010, 40, failed

so the result of the query should be like this-
1111, 1, 2010, 40, 3(count numOfFails)

but I get always other year and grade like-
1111, 1, 2009, 30, 3

I made a view to order the table by studId, year DESC but I still get the same results as with the unordered table. since I can't use the ORDER BY before I do the GROUP BY to get the numOfFails I don't know what to try anymore..

here's my SQL for now-

SELECT * , count( viewstudent_course_orderedby.courseId ) AS numOfFails
FROM viewstudent_course_orderedby
WHERE viewstudent_course_orderedby.courseId = '1111'
GROUP BY viewstudent_course_orderedby.studentId
  • 2 Contributors
  • 5 Replies
  • 241 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by 54uydf

Dani AI

Generated

A reliable way to get (a) the last year a student took a course, (b) the grade from that last attempt, and (c) how many times they failed, is to separate the “get the latest attempt” step from the “count failures” step. pointed you in the right direction with MAX(yearTaken) and confirmed it worked, but selecting grade alongside an aggregate can be nondeterministic (or rejected under modern MySQL ONLY_FULL_GROUP_BY). The safe patterns below return the correct grade for the latest year and an exact fail count.

Use a subquery that finds the MAX(yearTaken) per student/course, join it back to the attempts table to pick the matching row (this guarantees the grade belongs to the last year), and use conditional aggregation to count fails:

-- derived-table + join (works on older and newer MySQL)
SELECT latest.studId,
       s.name AS studName,
       sc.yearTaken     AS lastYear,
       sc.grade         AS lastGrade,
       counts.fail_count AS numFails
FROM (
  SELECT studId, courseId, MAX(yearTaken) AS yearTaken
  FROM student_course
  GROUP BY studId, courseId
) AS latest
JOIN student_course sc
  ON sc.studId = latest.studId
 AND sc.courseId = latest.courseId
 AND sc.yearTaken = latest.yearTaken
LEFT JOIN (
  SELECT studId, courseId, SUM(CASE WHEN passfail = 'failed' THEN 1 ELSE 0 END) AS fail_count
  FROM student_course
  GROUP BY studId, courseId
) AS counts
  ON counts.studId = latest.studId AND counts.courseId = latest.courseId
LEFT JOIN students s ON s.id = latest.studId;

If you run MySQL 8.0+, window functions give a compact option: rank attempts by year and use SUM(...) OVER to get fail totals, then pick the row where ROW_NUMBER() = 1.

Notes and gotchas: add an index on (studId, courseId, yearTaken) or (courseId, studId, yearTaken) for speed. If multiple attempts share the same year, add a tie-breaker (timestamp or attempt_id) to the MAX/ORDER BY. To limit the report to students who actually failed, filter where fail_count > 0 or include a WHERE clause that matches your fail definition (passfail flag, or grade < threshold).

Recommended Answers

All 5 Replies

Member Avatar for debasisdas

Why you need YEAR if try to display all the failures ?

Does the same course id repeats across all the years ?

Member Avatar for debasisdas

Why you need YEAR if try to display all the failures ?

Does the same course id repeats across all the years ?

Member Avatar for 54uydf

I want the report to show when was the last time the student took the course AND what grade he got on his last try AND how many times he took the course in total

the courseId stays the same

Member Avatar for debasisdas

try this

select studId, courseId, max(yearTaken), grade, count(courseid) as no_of_times
from table
group by studId, courseId
Member Avatar for 54uydf

IT WORKS!!!!!!!!!!!!!!
thank you thank you thank you!!!

Edited by 54uydf because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.