Grouping in MS SQL

Question

I got a question with SQL if you have a quick minute.

The below is how my data looks like. Now I am trying to write a query to categorize a ID number on basis of LOGGING STATUS as below. The output ID status needs to show as one row (one value).

If a ID # 9630 has logging status as only fail then the output would be FAIL

ID     STATUS   ZIP-CODE
9630    FAIL        8959
9630    FAIL        8959

If the ID # 19630 has logging status as only success then the output would be SUCCESS

ID      STATUS     ZIP-CODE
19630   SUCCESS      72259
19630   SUCCESS      72216

If the id # 1963 has load status as fail and success like below then the output would be BOTH

ID    STATUS    ZIP-CODE
18963   fail      92259
18963   success   96216
18963   fail      62296

I am trying to find out how could I write the SQL to achieve the output as above.

Answer 1

SELECT id,IIF(MIN(status)=MAX(status),MAX(status),'both') AS [status]
FROM t
GROUP BY id

Answer 2

There's a bit of ambiguity to what the actual end goal is here but from my interpretation this is what I'd write in this case.

SELECT DISTINCT ID, STATUS = 
  CASE StatusCheck
     WHEN MIN(STATUS) >= MAX(STATUS) THEN MIN(STATUS)
     WHEN MAX(STATUS) >= MIN(STATUS) THEN MAX(STATUS)
     ELSE 'BOTH'
   END,
 [ZIP-CODE]
 FROM [LOGGING STATUS].[STATUS]
ORDER BY ID

You should also consider looking into the GROUP-BY clause in T-SQL considering you're using MSSQL. That might also be the place to start if this isn't exactly what you're looking for.

Answer 3

Perhaps this is what you want:

select id,
       (case when min(status) = max(status) then max(status) else 'both'
        end) as status
from t
group by id;

If you just want the value for a given status:

select (case when min(status) = max(status) then max(status) else 'both'
        end) as status
from t
where id = @id;