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:
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
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