COUNT GRADES

[MAZLAN]

I have given grades to my students from A - E for nine subjects. My problem is how to count how many A's, B's ...E's.. they got for every student out of nine subject that they take.

 

[Mitch]

What is the structure of your table(s) ? is it relational or just a single fixed table ?

Mitch.

 

[MAZLAN]

Its a relational table. One for the marks and the other is the table for the grades.

 

[Pat Hartman]

Select StudentID, Grade, Count(*) AS CountOfGrades
From GradeTable
Group By StudentID, Grade;

This will produce the following:
StudentID..Grade..CountOfGrades
233........A......2
233........C......4
233........D......3
245........B......5
245........C......4

 

[MAZLAN]

Thanks Mr. Pat for your suggestion but i'm not sure how to implement it because I have 9 grades fields for 9 subject. For your information, I am using duplicate grade tables for every subject. I dont have StudentID fields in Grade Table. It consists of number 1 - 100 in marks field and the grade at the other field. I join the marks field of the student table and the marks field of the grade table to get the grades. I have 9 duplicates grade tables to do that.

 

[Pat Hartman]

You've made a lot of unnecessary work for yourself by creating 9 tables to hold data that should be stored in a single table. You'll need to run the count query, 9 times. Once for each table. You'll also need to use a join in the query:

Select G.StudentID, M.Grade, Count(*) AS CountOfGrades
From GradeTable As G Left Join MarksTable As M On G.Marks = M.Marks
Group By G.StudentID, M.Grade;