Help with sql query

Question

I have two tables: subject and student. I'm trying to count the number of subjects enrolled by each student. How do I do that?I'm trying the code below but it doesn't give the answer I need. Please help.

SELECT COUNT( subject.SUBJECT ) , student.IDNO, student.FIRSTNAME, subject.SUBJECT
FROM student, subject
GROUP BY subject.SUBJECT
LIMIT 0 , 30

Answer 1

You are missing a join condition. Somewhere you are storing info about what subject each student is taking. Perhaps WHERE subject.student_id = student.id.

Answer 2

you do have an Enrolment table too, don't you? to resolve the many-to-many relationship between student and subject. you could work with just that. say, your enrolment table has studentID and subjectID columns, then you would only need:

SELECT studentID, COUNT(*) FROM Enrolment GROUP BY studentId;

or, to include their names too,

SELECT s.firstname, COUNT(*) FROM Enrolment e JOIN student s on e.studentId=s.IDNO GROUP BY e.studentId;