# Are students using 6 rooms at the same time for 6th grade?

My doubt is of logical nature.

Querying the database, I can understand that 6th grade students are - at any given period - using 6 different 6th grade rooms.

In other words the 6th grade student are being split between 6 rooms depending on their schedule.

So, to understand the total capacity the school has for 6th grade rooms, shouldn't we sum 6th grade room's capacity?

I see what you are suggesting, but I think the issue is that each student must take all classes. So at some point every student would spend one period in the smallest room. So to account for that, the capacity is that of the smallest room that is used for 6th grade classes multiplied by the number of periods in the day.

Your suggestion would work if students were allowed to skip the class given in the smallest room entirely, and be in one of the larger rooms more than once a day.

I see what you mean. Ok I think I got it now. Thanks for the clarification Steven!

Ah! I think I was falling in the same hole. I was summing the capacity of 6th grade rooms for each period. I guess the problem with that is, the school can change around which 6th grade rooms are scheduled for each period. Is my logic on the right track?

```SELECT PERIOD_ID, SUM(CAPACITY) FROM ROOMS
JOIN CLASSES ON CLASSES.ROOM_ID = ROOMS.ID
JOIN SUBJECTS ON SUBJECTS.ID = CLASSES.SUBJECT_ID
GROUP BY PERIOD_ID
```

Yes, I'm appreciating this conversation, as it's giving light to an unspoken assumption. My assumption is that, in a hypothetical example, a required Algebra 1 class can happen in room 101 and 102, and a student need only attend one of them. However, in this school's database, 6th grade Math only occurs in Room 13, across periods 1-7. Here's the query I used

```SELECT SUBJECTS.GRADE, CLASSES.ROOM_ID
, SUBJECTS.NAME
, ROOMS.CAPACITY
, SUM(ROOMS.CAPACITY)
FROM SUBJECTS
INNER JOIN CLASSES
ON CLASSES.SUBJECT_ID = SUBJECTS.ID
INNER JOIN ROOMS
ON CLASSES.ROOM_ID = ROOMS.ID
GROUP BY CLASSES.ROOM_ID
```

-- Do they have room for that many 6th graders?

```SELECT DISTINCT(classes.room_id) AS "Room #", subjects.grade AS "Grade", subjects.name AS "Course", periods.id AS "# of Periods", rooms.capacity AS "Room Capacity per Period" FROM CLASSES
JOIN SUBJECTS ON subjects.id = classes.subject_id
JOIN ROOMS ON rooms.id = classes.room_id
JOIN PERIODS ON periods.id = classes.period_id
GROUP BY subjects.name HAVING subjects.grade = 6
ORDER BY classes.room_id;

SELECT DISTINCT(classes.room_id) AS "Room #", subjects.grade AS "Grade", subjects.name AS "Course", periods.id AS "# of Periods", rooms.capacity AS "Room Capacity per Period" FROM CLASSES
JOIN SUBJECTS ON subjects.id = classes.subject_id
JOIN ROOMS ON rooms.id = classes.room_id
JOIN PERIODS ON periods.id = classes.period_id
GROUP BY subjects.name
ORDER BY classes.room_id;

SELECT 30*4+35*2 AS "Current 6th Grade Capacity per Period";

SELECT "School needs to fund for outside expansion of P.E. & use Room #18 to accommodate next year's in-coming class expansion, use Room #20 (Woodshop Room) for the other 2 periods and/or Room #23 (Puppetry Room) for the other 3 periods as the capacity sizes are much smaller, or go to block scheduling so rooms can be used multi-purpose" AS "Answer";
```

Seems to be the best solution to me.

This won't give you a correct result. It is adding up the room ID numbers with no concern for capacity!

If you fix that, you still need to account for the smallest room being the limiting factor.