Case when statement to categorized values

Question

TABLE_1:

Col_1	Col_2	Col_3
A	Apple	100
A	Apple	100
A	Apple	56
A	Apple	44
B	Banana	100
C	Cucumber	93
C	Cucumber	7

I want to use a case when statement to categorize Col 1 as "good" or "bad" into a new column. I have come up with the following query:

SELECT DISTINCT
   Col_1
   , CASE WHEN Col_3 = 100 THEN 'good'
      ELSE 'bad'
   END AS STATUS
FROM TABLE_1

This results in the TABLE_2:

Col_1	STATUS
A	good
A	bad
B	good
C	bad

How do I fix this so that if A is both good and bad, it will just take the bad status? I would like TABLE_3 to be my result

TABLE_3:

Col_1	STATUS
A	bad
B	good
C	bad

Answer 1

Something like this, perhaps?

Sample data:

SQL> with table_1 (col1, col2, col3) as
  2    (select 'A', 'Apple',  100 from dual union all
  3     select 'A', 'Apple',  100 from dual union all
  4     select 'A', 'Apple',   56 from dual union all
  5     select 'A', 'Apple',   44 from dual union all
  6     select 'B', 'Banana' ,100 from dual union all
  7     select 'C', 'Cucumber',93 from dual union all
  8     select 'C', 'Cucumber', 7 from dual
  9    )

Query:

 10  select col1,
 11    case when max(col3) = 100 and min(col3) < 100 then 'bad'
 12         when max(col3) = 100                     then 'good'
 13         else 'bad'
 14    end status
 15  from table_1
 16  group by col1;

COL1  STATUS
----- ----------
A     bad
B     good
C     bad

SQL>

---

Or even simpler

 10  select col1,
 11    min(case when col3 = 100 then 'good'
 12             else 'bad'
 13        end
 14       ) status
 15  from table_1
 16  group by col1;

COL1  STATUS
----- ----------
A     bad
B     good
C     bad

SQL>