SQL - CASE WHEN count different values

Question

I need to show how many different values every 'id' has.

It should look like this:

id    |  component_a | component_b | component_c
--------------------------------------------------
KLS11 |     none     |      one    |     none       
KLS12 |     one      |      one    |     none         
KLS13 |     several  |      one    |     none        
KLS14 |     one      |      one    |     one            
KLS15 |     one      |    several  |     several           

I have the following table (table_a):

id    |  component_a | component_b | component_c
--------------------------------------------------
KLS11 |              |      a      |            
KLS12 |       a      |      a      |              
KLS13 |       a      |      a      |             
KLS13 |       b      |      a      |               
KLS14 |       a      |      a      |      a        
KLS15 |       a      |      a      |      a                
KLS15 |       a      |      b      |      b

Here an example/explanation:

• KLS13 has different values in component_a ( a,b ) - so it should display 'several'
• KLS13 has the same values in component_b ( a,a ) - so it should display 'one'
• KLS13 has no value in component_c - so it should display 'none'

Here's my SQL-code:

I already did it for component_a but it doesnt work. What am i doing wrong?

SELECT 
CASE WHEN component_a is NULL THEN 'none'
     WHEN (SELECT count(DISTINCT component_a) 
             FROM table_a
              WHERE id=(SELECT id 
                          FROM table_a GROUP BY id HAVING count(*)>1)>1 THEN 'several'
     WHEN (SELECT count(DISTINCT component_a) 
             FROM table_a
              WHERE id=(SELECT id 
                          FROM table_a GROUP BY id HAVING count(*)>1)=1 THEN 'one'
END as componentA
FROM table_a

i am a beginner at SQL so i would appreciate any help.

Have a nice day

Answer 1

You're getting an ORA-00936 error (I think) because you aren't closing the parentheses within each when branch; adding an extra close changes the error to 'ORA-01427: single-row subquery returns more than one row', because the sub-sub-select (with the having clause) returns multiple rows - there's no correlation.

You don't need the sub-queries, you just need to count the distinct values as part of the case construct, to create a searched case expression:

select id,
  case count(distinct component_a)
    when 0 then 'none'
    when 1 then 'one'
    else 'several'
  end as component_a
from table_a
group by id
order by id;

ID    COMPONENT_A
----- -----------
KLS11 none        
KLS12 one         
KLS13 several     
KLS14 one         
KLS15 one         

And repeat for the other columns:

select id,
  case count(distinct component_a)
    when 0 then 'none'
    when 1 then 'one'
    else 'several'
  end as component_a,
  case count(distinct component_b)
    when 0 then 'none'
    when 1 then 'one'
    else 'several'
  end as component_b,
  case count(distinct component_c)
    when 0 then 'none'
    when 1 then 'one'
    else 'several'
  end as component_c
from table_a
group by id
order by id;

ID    COMPONENT_A COMPONENT_B COMPONENT_C
----- ----------- ----------- -----------
KLS11 none        one         none        
KLS12 one         one         none        
KLS13 several     one         none        
KLS14 one         one         one         
KLS15 one         several     several