Austin Hunter Austin Hunter - 1 month ago 5
MySQL Question

SQL keeping count of how many times

I have a table of grades/courses. I am trying to see how many times a person has taken a course, and also return the highest grade they have achieved.

So far I have:

SELECT tnumber, courseid, grade FROM grades


I need to display all tnumbers that have taken the same course more than once. Also list the students highest grade for the course.

How do you keep count of how many times a course has occurred?
I want to display the student's tnumber, course_id, attempts, and highest grade. And all within a single query.

If I need to show an example of my table/s. Let me know and Ill post them. I just don't want to overload the page with code!

Thanks.

Answer

For example (table alias G not necessary as only 1 table is involved using for example only)

SELECT g.tnumber, g.courseid, min(g.grade), count(*)
FROM grades g
GROUP BY g.tNumber, g.CourseID

This returns the tnumber and courseID for each record with the highest (alphabetic grade) and a count of how many records exist for that same tnumber and courseID. You could use count(*) or count(1).

Based on a prior posts I'm assuming grade is alphabetic.

Comments