Post reply

Counting Classes

  • September 23, 2015 at 10:33 pm

    #75728

    Comments posted to this topic are about the item Counting Classes

  • September 24, 2015 at 12:39 am

    #1829024

    First query has an extra ; before ORDER BY, just a comment.

  • September 24, 2015 at 1:08 am

    #1829034

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 24, 2015 at 1:49 am

    #1829039

    I am not convinced that this is the right answer to the general problem. It works with that particular data set only because there are no duplicate subjects. We were asked for a count of subjects, not classes, which is what option 2 returns. If there had been, for example, another English class in the same room later in the day, the query will return the class count of 4 for building 2, classroom 2, not the subject count, which should still be 3.

    So, either badly worded question, or no correct answer supplied - take your pick.

  • September 24, 2015 at 2:46 am

    #1829057

    I agree; poor question with no correct answer. How can a count of rows in a table that lists Classes return a "count of subjects in each"?

    A Subject is "English" or "Chemistry", a Class is a Subject in a Room in a Time Slot. Without eliminating the Time Slot variation it is not possible to answer the question.

    Add the row (1, 2, 'Algebra', '12:00') to the data set and every option returns incorrect results. The only way to know that query two is going to return the correct result is if the data set had been examined in detail in advance.

    I only entered an answer in order to get access to the discussion

  • September 24, 2015 at 3:36 am

    #1829066

    I got it right, but with the wrong reasoning. My rationale was that there is a trivial way of getting the answer we need, so it's unlikely that SQLServer would be written to choose a better plan for something obscure than it would for something basic.

  • September 24, 2015 at 4:04 am

    #1829071

    matthew.flower (9/24/2015)

    I agree; poor question with no correct answer. How can a count of rows in a table that lists Classes return a "count of subjects in each"?

    I can see where you are going at, but why is "count of subjects" interpreted by you as "count of unique/distinct subjects"? The following query returns for me correct counts, just depending on what you want.

    SELECT

    building,

    classroomnumber,

    TotalSubjects = COUNT(classsubject),

    TotalDistinctSubjects = COUNT(DISTINCT classsubject)

    FROM

    dbo.classes

    GROUP BY

    building,

    classroomnumber

  • September 24, 2015 at 4:17 am

    #1829072

    Because there is a Column called ClassSubject - a count of Classes in each Room (and Building) is a completely different question to a count of Subjects.

    They are different types of Object and the implied 3NF design would also have Class_Subject, Building and it's child table Class_Room and Class_Time_Slot tables with this table being a link table between Subject, Class_room and Class_Tine_Slot.

    It is like counting the "cars" driving down the road in an hour and answering 1000 when the wanted answer was 4 because the real question was to count the different colours of cars driving down the road.

  • September 24, 2015 at 5:38 am

    #1829089

    Maybe reading the requirements is a part of the question. The "count of subjects" is different than the "count of distinct subjects". So very many times in work, we really have to listen to what people are telling us they want. This, along with the conversation, determine what we need to produce and how we're going to write it. The first step in gathering requirements is listening and reading.

    Steve, I didn't think there was anything wrong with question.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • September 24, 2015 at 5:53 am

    #1829091

    I disagree. This question had no correct answer. A subject is not the same thing as a class. The correct answer as it is worded would be to count distinct subjects.

    I've worked on classroom scheduling software in the past and my experience tells me that this is a poorly worded question. As an analyst/developer, I would have gone back to the requestor for clarification on whether they want to know how many subjects are taught in each room or how many classes are being taught in each room. The answer to this question could be two very different metrics. One is telling us whether we have rooms that will be used for multiple subjects, which may indicate that there was a mistake with the scheduling (each subject is going to have its own requirements for how the room be set up - you wouldn't use a physics lab to teach an art class). The other answer is telling us the total number of classes taught in the same room - a very different metric that serves a different purpose.

  • September 24, 2015 at 6:44 am

    #1829107

    Ed Wagner (9/24/2015)

    Maybe reading the requirements is a part of the question. The "count of subjects" is different than the "count of distinct subjects". So very many times in work, we really have to listen to what people are telling us they want. This, along with the conversation, determine what we need to produce and how we're going to write it. The first step in gathering requirements is listening and reading.

    Steve, I didn't think there was anything wrong with question.

    How many subjects do they teach in college?

    Would you answer that question with the number of distinct subjects, or a count of every class, subject combination taught?

    In general, when people say how many x, "distinct" is implied.

    For another example, if I ask you how many cars you see in a parking lot, the answer may be 500. But if I ask you how many models of car you see in that same parking lot, I most likely do not want an answer of 500.

    This is where reading (and writing) requirements becomes an iterative process. I would say that the requirements are poorly written because they request an aggregate of a non-unique element without specifying whether a distinct aggregate is desired, and I would ask the person who wrote them to clarify.

  • September 24, 2015 at 6:45 am

    #1829108

    Mauricio_ (9/24/2015)

    First query has an extra ; before ORDER BY, just a comment.

    I noticed the same thing.

  • September 24, 2015 at 6:47 am

    #1829109

    Thanks for the exercise.

  • September 24, 2015 at 7:31 am

    #1829140

    I agree; poor question with no correct answer. How can a count of rows in a table that lists Classes return a "count of subjects in each"?

    I agree with all the comments on these lines. I ruled out number 2 because had the same subject been taught in the same room at a different time, it would not have told me the number of subjects being taught, but the number of classes taught. I actually wasn't sure my choice was correct, but having ruled out 2-4, only 1 was left.

    It could have been more accurately worded.

  • September 24, 2015 at 7:45 am

    #1829148

    I guess we are making a big thing out of something small. Steve wrote "subject" but meant "class" (as it even shows in the alias for the count field in solution 2). But to define that as "poorly worded" goes a bit too far for me.

    Besides that, with this data it did return the correct answer.

    And furthermore, the question was "Which query will work best?". So from these 4 answers this was the best one, but maybe not the perfect one...

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply