I have a table like this:
Student_id char(10
classroom_code char(5)
school_code char(4)
class_count int(4)
school_count int(4)
district_count int(5)
It contains a school district's population of kids for a single grade level (3643 students). I want to do cascading counts for the class, school, and district in the last 3 columns. Thus:
1. counts of pupils within classes (homeroom): 1...27, 1..19, 1..24, etc.
2. counts of pupils within schools (school code): 1..332, 1..419, 1..289
3. counts of pupils within district (district code): 1..3643
I can do the district count (a straight shot from 1 to 2643, but I cannot do the class and school counts, as they would need to repeatedly reset back to 1 to begin again.
Added simple example using unimaginably small classes and schools:
student_id class_code school_code class_count school_count district_count
12345 a-21 schA 1 1 1
12346 a-21 schA 2 2 2
12347 a-21 schA 3 3 3
12348 b-22 schA 1 4 4
12349 b-22 schA 2 5 5
12350 b-22 schA 3 6 6
12351 c-25 schBB 1 1 7
12352 c-25 schBB 2 2 8
12353 c-25 schBB 3 3 9
12354 c-25 schBB 4 4 10
12355 d-53 schBB 1 5 11
12356 d-53 schBB 2 6 12
12357 d-53 schBB 3 7 13
12358 d-53 schBB 4 8 14
... ... ...
Thanks for some help.