Looping counts for classroom

[forsooth]

I have a simple table like this:

    Student_id           char(10)
    classroom_code       char(5)
    class_count          int(4)

It contains a school district's population of kids for a single grade level (3643 students). I want to do cascading counts for the classrooms.

It is difficult because the counts need to repeatedly reset back to 1 to begin again.

Simple example using unimaginably small classes and schools:

    student_id  class_code   class_count (might look like this...)
    12345       a-21              1
    12346       a-21              2
    12347       a-21              3
    12348       b-22              1
    12349       b-22              2
    12350       b-22              3
    12351       c-25              1
    12352       c-25              2
    12353       c-25              3
    12354       c-25              4
    12355       d-53              1
    12356       d-53              2
    12357       d-53              3
    12358       d-53              4
    ...         ...          ...          

What would be the MySQL code to do this? Thanks for any help.

[dagon]

why take class count out of the table, add it with php when you do the output

[dagon]

or ....

select l.Student_id, l.classroom_code, count(*) as num
from STUDENTS as l
left outer join STUDENTS as r
on l.Student_id = r.Student_id
and l.classroom_code >= r.classroom_code
group by l.Student_id, l.classroom_code;

[forsooth]

Thank you, dagon...I'm falling asleep at the computer now. I will implement this first thing tomorrow and get back to you. I did not think of doing a self-join.
🙂

[dagon]

i fess up, i found that trick with google.