Column with row counts...counting kids within classes, schools, and district

[mrherman]

Hi,

 

In 3 columns of student information, I'd like to have 3 different counts.

1. students within classes (homeroom): 1...27, 1..19, 1..24, etc.
2. students within schools (school code): 1..332, 1..419, 1..289
3. students within district (district code): 1..3643

 

I can do the district count, but the others are beyond me.

 

Thanks for any help!

 

[mikosiko]

You should post at least your table(s) structure and the code that you have done so far... that way will be easier to get some help 

[mrherman]

For table structure, here is a simple representation:

 

Student_name         char(35)
Classroom_location   char(5)
class_count          int(4)
school_count         int(4)
district_count       int(5)

 

I don't have any specific code, as I cannot conceive how it might be written.  My "group by" attempts don't even come close.

 

Thanks.

 

 

 

[mikosiko]

Is that your "REAL" database table?.... it doesn't looks very well designed or you posted incomplete information... in the way you are showing it you will have a lot of duplicated information ... for each student row you are going to have the classroom_location, class_count, school_count and district_count rows?... no good.  (I'm not talking here of how you want to present your data... I'm talking about how you want to store it). And in addition, with that structure is not possible to resolve your questions.

 

Seems that what you want to model is something more or less in this lines:

- A District table  (district_id, district_name)

- A School Table  (school_id, school_name, district_id)

- A Classroom Table  (classroom_id, classroom_location)

- A Student Table (student_id, school_id, classroom_id, student_name)

 

with a model like this you can solve your query easily using the student table..

 

hope this help... if not... post more complete information