Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    14

    Question Unanswered: How would I group a table by values in one column and something else.

    The reason the title says "something else" is because I couldn't fit the entirety of my question in it. But here goes:

    Hi, guys. Just joined the forum.

    My question regards grouping. Say I have this table here:

    Team_Num | Name | Status
    1 Jim up
    1 Job up
    1 Bob down
    2 Dan down
    2 Luke down
    7 Han up

    I want to group people by teams and I want the team to have an overall status such that if any member is 'up' the entire team is 'up.' Likewise, if all members of the team are 'down,' only then is then entire team down. Like an OR statement, obviously. So the resulting table would look:

    Team_Num | Team_Status
    1 up
    2 down
    7 up

    Easy enough, right? The trick is that I can't make an actual table with this. This second result you see here has to be the result of a query. In the database there will be only ONE table (the first one at the beginning of the post). I basically need one query that would deliver this derived table. Any help?

    Also, does anyone know an attractive way to show a table on this forum?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Team_Num
         , CASE WHEN MAX(status) = 'up'
                THEN 'up'
                ELSE 'down' END AS Status 
      FROM daTable
    GROUP
        BY Team_Num
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    Code:
    SELECT Team_Num
         , CASE WHEN MAX(status) = 'up'
                THEN 'up'
                ELSE 'down' END AS Status 
      FROM daTable
    GROUP
        BY Team_Num
    Thank you. I think this works. I'll test it out a bit more with my actual table and then I'll mark this thread as solved.

  4. #4
    Join Date
    Jan 2012
    Posts
    14

    Exclamation How can I use that derived column "TeamStatus" in another case?

    Okay so the previous problem is solved but now I have a new issue. Say I want to add another column, to the second (derived table) based on the value in "Team_Status." This new column (call it "The_Date") would have a value from another query. I want to run code like this, but I'm not sure how the case stuff really works
    Code:
    SELECT Team_Num
         , CASE WHEN MAX(status) = 'up'
                THEN 'up'
                ELSE 'down' END AS Team_Status
         , CASE WHEN Team_Status = 'down'
                THEN (SELECT someDate FROM someOtherTable WHERE Team_Num=this.Team_Num)
                ELSE 'N/A' END AS The_Date
      FROM daTable
    GROUP
        BY Team_Num
    If doesn't make sense, please don't hesitate to ask for clarification.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Team_Num
         , Team_Status
         , CASE WHEN Team_Status = 'down'
                THEN ( SELECT CAST(someDate AS CHAR) 
                         FROM someOtherTable 
                        WHERE Team_Num = this.Team_Num )
                ELSE 'N/A' END AS The_Date
      FROM ( SELECT Team_Num
                  , CASE WHEN MAX(status) = 'up'
                         THEN 'up'
                         ELSE 'down' END AS Team_Status
               FROM daTable
             GROUP
                 BY Team_Num ) AS this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2012
    Posts
    14
    Wow, you're really smart. Whatever they pay you at your job, it isn't enough.
    Last edited by HunterDX77M; 02-09-12 at 15:03. Reason: Fixed my issue

Tags for this Thread

Posting Permissions

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