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

HunterDX77M · #1 (**permalink**) 01-31-12, 20:19

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?

r937 · #2 (**permalink**) 01-31-12, 22:01

Code:

SELECT Team_Num
     , CASE WHEN MAX(status) = 'up'
            THEN 'up'
            ELSE 'down' END AS Status 
  FROM daTable
GROUP
    BY Team_Num

HunterDX77M · #3 (**permalink**) 02-01-12, 18:37

Quote:

Originally Posted by r937

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.

HunterDX77M · #4 (**permalink**) 02-07-12, 17:02

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.

r937 · #5 (**permalink**) 02-07-12, 17:45

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

HunterDX77M · #6 (**permalink**) 02-09-12, 13:52

Wow, you're really smart. Whatever they pay you at your job, it isn't enough.

Internet Services

Web Development

Digital Marketing

Consumer Tech