Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2011
    Posts
    10

    Unanswered: T-Sql - Compare rows to determine if a column's values are different

    Hello,

    I need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.

    I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.

    Course table:
    CourseID, Desc
    1 English
    2 History
    3 Science

    CourseDays table:
    CourseID, Day, RoomID
    1 M 320
    1 W 445
    1 F 680
    2 T 123
    2 Th 123
    3 M 514
    3 T 514
    3 W 521
    3 Th 901
    3 F 521

    Expected results:
    CourseID, Desc, RoomID
    1 English Various
    2 History 123
    3 Science Various

    I simplified the example, as there are about 30 columns in the table and about half a million rows.

    If DDL is needed, I can create it. Any help would be much appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StratakesGC View Post
    This is a work assignment; I am not a student.
    yeah, right, pull my other leg

    half a million rows of course id, day, roomid -- a work assignment indeed

    but hey, i can help you with it anyway

    please show us a query that counts the number of days per course, and i'll show you how to modify it to get what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2011
    Posts
    10
    OK. Here is the actually query that will count the number of days per course

    Select count(AdClassSchedDay.AdClassSchedID) as NumCourseDays
    from AdClassSchedDay
    Group by AdClassSchedDay.AdClassSchedID

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StratakesGC View Post
    OK. Here is the actually query that will count the number of days per course
    excellent

    actually, you can go ahead and use COUNT(*) instead of COUNT(AdClassSchedID), unless you have rows where AdClassSchedID is null in that table

    okay, let's move on to the second stage...

    can you modify this query so that it not only counts the number of days but also the number of wednesdays

    hint: you'll need the IIF function (as this appears to be msaccess)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2011
    Posts
    10
    A class can only be scheduled once per day, so either the class is scheduled on a Wednesday or it isn't. If a course was scheduled at a different time during the same day, it would be setup as a new class. This is why the AdClassSched and AdClassSchedDay tables are so large.

    I am using SQL Server 2005, so instead of IIF, I tried using a CASE statement.

    I tried the following, but this affected the NumCourseDays count because of the AdClassSchedDay.DayofWeek in the Group By:

    Select count(*) as NumCourseDays,
    Case AdClassSchedDay.DayofWeek When 4 then 1 Else 0 End as NumWednesday
    from AdClassSchedDay
    Group by AdClassSchedDay.AdClassSchedID, AdClassSchedDay.DayofWeek

    I know this is not counting the number of Wednesdays for a class, but I am unsure of the logic to do so. Do I need to create a sub-query?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you were definitley on the right track
    Code:
    SELECT COUNT(*) AS NumCourseDays
         , COUNT( 
             CASE WHEN DayofWeek = 4 
                  THEN 'wed'
                  ELSE NULL END 
                ) AS NumWednesday_1
         , SUM( 
             CASE WHEN DayofWeek = 4 
                  THEN 1
                  ELSE 0 END 
                ) AS NumWednesday_2
      FROM AdClassSchedDay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2011
    Posts
    10
    Just to make sure, I had to add the GROUP BY to your select statement, right? The logic looks so simple, not sure why I am having such a hard time with this.

    So the next step would be to count the number of times a room is used for a class and compare it to the number of days the class is given. If the number of days a room is used is less than number of days a class is given, than the class is held in more than one room.

    Unlike DayOfWeek where the values are fixed (1-7), room number is variable, so I am not sure how to modify your code to count the number of times a room number is used.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StratakesGC View Post
    Just to make sure, I had to add the GROUP BY to your select statement, right?
    no, sorry, you didn't

    unless you wanted subtotals, and what you got would depend on what you had in the GROUP BY

    Quote Originally Posted by StratakesGC View Post
    The logic looks so simple
    thank you

    it not only looks simple, it is

    line breaks and indentation do improve ease of comprehension, wouldn't you say?

    i gave you two different ways of doing the wednesday counts -- you should stop and understand how they work

    Quote Originally Posted by StratakesGC View Post
    So the next step would be to count the number of times a room is used for a class and compare it to the number of days the class is given. If the number of days a room is used is less than number of days a class is given, than the class is held in more than one room.
    definitely on the right track
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2011
    Posts
    10
    In your first example, NumWednesday_1, if the DayOfWeek is 4 (Wednesday), then set a value that will be counted. If the DayOfWeek is not 4, then set the value to Null. Null values will not be counted.

    In your second example, NumWednesday_2, if the DayOfWeek is 4, set value to 1, else set the value to 0. Sum all the values for DayofWeek. Since only days that have a 4 get a value of one, only days with a 4 will be summed up.

    I know this doesn't work, but this is how I see it:
    Code:
    SELECT AdClassSchedID
    	, SUM(
    	      CASE WHEN COUNT(AdRoomID) = COUNT(AdClassSchedID) 
    		 THEN 1
    	               ELSE 0 END
    			) AS MultRooms
      FROM AdClassSchedDay
    What am I missing?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StratakesGC View Post
    In your first example, NumWednesday_1, if the DayOfWeek is 4 (Wednesday), then set a value that will be counted. If the DayOfWeek is not 4, then set the value to Null. Null values will not be counted.

    In your second example, NumWednesday_2, if the DayOfWeek is 4, set value to 1, else set the value to 0. Sum all the values for DayofWeek. Since only days that have a 4 get a value of one, only days with a 4 will be summed up.
    that's exactly right, and well done

    i personally prefer counting non-nulls to summing 0s ans 1s

    what you're missing on the next query is the GROUP BY clause, and that SUM expression has to be re-written into a HAVING clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2011
    Posts
    10
    Is this what you were expecting?
    Code:
    SELECT AdClassSchedID
    	 , COUNT(*) AS NumCourseDays
         , SUM( 
             CASE WHEN [DayofWeek] = 4 
                  THEN 1
                  ELSE 0 END 
                ) AS NumWednesday_2
    FROM AdClassSchedDay
    Group BY AdRoomID, AdClassSchedID
    HAVING SUM( 
             CASE WHEN [DayofWeek] = 4 
                  THEN 1
                  ELSE 0 END 
                ) = 4

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StratakesGC View Post
    Is this what you were expecting?
    you're definitly on the right track

    your GROUP BY clause is weird, you have two columns in it, but you're only showing one of them in the SELECT clause -- why?

    your HAVING clause can actually use the column alias assigned to the SUM in the SELECT clause

    so right now your query returns all AdRoomID/AdClassSchedID combinations which have 4 wednesdays

    is that what you were expecting?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2011
    Posts
    10
    your GROUP BY clause is weird, you have two columns in it, but you're only showing one of them in the SELECT clause -- why?
    I forgot to remove it. I was selecting the AdRoomID, but removed it from the SELECT clause but not the GROUP BY clause before I posted it.

    your HAVING clause can actually use the column alias assigned to the SUM in the SELECT clause
    I understand the reason this works; the HAVING clause is evaluated before the SELECT clause, but I can't get the syntax right, and haven't found any examples on the Internet. Would you mind posting an example of using an alias in the HAVING clause?

    so right now your query returns all AdRoomID/AdClassSchedID combinations which have 4 wednesdays is that what you were expecting?
    Yes, was was expecting all AdRoomID/AdClassSchedID combinations which have 4 - wednesdays.

    Let's talk about the inital issue. If all the days for a class are in the same room, return the room number, else return 'various'. I don't see how limiting the data with a HAVING clause will help.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StratakesGC View Post
    Would you mind posting an example of using an alias in the HAVING clause?
    i'll use your example
    Code:
    SELECT AdClassSchedID
         , COUNT(*) AS NumCourseDays
         , SUM( 
             CASE WHEN [DayofWeek] = 4 
                  THEN 1
                  ELSE 0 END 
                ) AS NumWednesday_2
      FROM AdClassSchedDay
    GROUP 
        BY AdClassSchedID
    HAVING NumWednesday_2 = 4
    Quote Originally Posted by StratakesGC View Post
    Let's talk about the inital issue. If all the days for a class are in the same room, return the room number, else return 'various'. I don't see how limiting the data with a HAVING clause will help.
    okay, let's talk about it

    first of all, why don't you write a query that counts how many different rooms are used for each class
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2011
    Posts
    10
    When I run that query I get:

    Msg 207, Level 16, State 1, Line 11
    Invalid column name 'NumWednesday_2'.

    I would have expected the alias to be named in the HAVING Clause since it is evaluated before the SELECT Clause.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •