Databases SQL Reporting by Example Day 3: Getting Good at Grouping Busiest Teachers

seth aruby
seth aruby
7,621 Points

Making query account for unknown PERIODS count

Shouldn't this query be written such that it is PERIODS count agnostic? Yes, the comment in the example tells you there are 7 periods but it seems like it would be better to have within the query some sort of check on the PERIODS table for the actual count of PERIODS currently in the db?

seth aruby
seth aruby
7,621 Points

ie, something like this...

SELECT t.id, t.first_name, t.last_name, COUNT(c.period_id) AS period_count from Teachers AS t
JOIN Classes AS c ON c.teacher_id=t.id
GROUP BY t.id HAVING period_count = (SELECT COUNT(ID) FROM PERIODS);

1 Answer

joelearner
joelearner
36,632 Points

Hi Seth,

Are you saying you want a way for the database to tell you how many periods are possible without the teacher telling you ahead of time? In that case, you can write a query to return the highest period number like this:

SELECT MAX(PERIOD_ID) AS HIGHEST_NUMBER FROM TEACHERS
JOIN CLASSES ON TEACHERS.ID = CLASSES.TEACHER_ID

Then you can change the query back to the way the teacher shows to retrieve the records matching the maximum period number.

Cheers!

seth aruby
seth aruby
7,621 Points

Hey Joe, Thanks for your help.

Yes that is what I was driving at. To me, the query should be more dynamic assuming first that the number of periods may change (say a school that is moving from a traditional schedule to a block schedule) and second that the individual writing the query will not have to update the query every time the school changes its schedule. I like what you have proposed using MAX() but I wonder if it is dynamic enough?? I'm thinking that doing a COUNT on the primary key column in the PERIODS table might be the best since even with MAX(PERIOD_ID) you could technically get into a situation where someone removed a PERIOD record between 1-7 and you'd really only have 7 periods but the MAX(PERIOD_ID) would return 8. I'm likely overthinking though.