SQL - CASE WHEN count different values
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
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
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