I'm attempting to run a query that adds up the total number of subjects in a class. A class has many subjects. There is a 'teachersclasses' table between teachers (the user table) and classes. The principles sounds pretty simple but I'm having some trouble in getting my page to display the number of subjects for each class (directly associated with the teacher)

This is what I have so far, trying to make use of the COUNT with a nested SELECT:

SELECT (SELECT count(*) FROM subjects WHERE subjects.classid = class.classid) AS total_subjects, class.classname, class.classid 

FROM class

Then I am calling up 'num_subjects' to present the total within a while loop:

            <?php echo $row['total_subjects']?>

From the above, I am receiving the total subjects for a class, but within the same table row (for one class) and my other while loop doesnt run anymore, which returns all of the classes associated with a teacher :( ... Bit of a mess now!

I know to return the classes for a particular teacher, I can do an additional WHERE clause on the session of 'teacherid' but I think my query is getting too complicated for me that errors are popping up everywhere. Anyone have a quick fix for this! Thanks very much