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

I think you need ELSE before END.Also didnt work is not much help. — Mihai, Apr 15 '14 at 08:51
What does "didn't work" mean? Do you get an error, or just not the results you wanted? — Alex Poole, Apr 15 '14 at 08:52

Alex Poole · Answer 1 · 2014-04-15T09:15:45.817

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

Hamidreza · Answer 2 · 2014-04-15T09:32:48.700

0

Try this query:

WITH t1 as
(SELECT COUNT(DISTINCT COMPONENT_A) COMPONENT_A,
COUNT(DISTINCT COMPONENT_B) COMPONENT_B,COUNT(DISTINCT COMPONENT_C) COMPONENT_C
FROM TABLE1 GROUP BY ID)
SELECT 
CASE 
WHEN COMPONENT_A = 1 THEN 'one'
WHEN COMPONENT_A > 1 THEN 'several' ELSE 'none' END AS COMPONENT_A,
CASE
WHEN COMPONENT_B = 1 THEN 'one'
WHEN COMPONENT_B > 1 THEN 'several' ELSE 'none' END AS COMPONENT_B,
CASE
WHEN COMPONENT_C = 1 THEN 'one'
WHEN COMPONENT_C > 1 THEN 'several' ELSE 'none' END AS COMPONENT_C
FROM t1;

edited Apr 15 '14 at 09:32

answered Apr 15 '14 at 09:15

Hamidreza

3,038
1
18
15

That only works with this specific data; if you add a value of `'c'` then it isn't counted at all, and if an ID only has `b` then it reports it as `several` instead of `one`, for example. There has to be a `count` somewhere. – Alex Poole Apr 15 '14 at 09:20
Yes you are right i didn't read all the question,thanks for your comment. – Hamidreza Apr 15 '14 at 09:23

SQL - CASE WHEN count different values

2 Answers2