CODEWITHSUNDEEP
sqloracle10g
king jia
king jia

Reputation: 712

Oracle 10g SQL: Return true if a column has only a value, but > 1 rows in a table

How to check a table if a column has only one value but multiple rows in a table??

For example:

ID        Reference        Status       
1         28910293         900
2         28910293         920
3         28910293         930

This will return true because the column(Reference) has only one value but multiple rows

ID        Reference        Status       
1         28910293         900
2         28910293         920
3         28910293         930
4         28910291         900

This will return false because the column(Reference) has 2 values.

Upvotes: 0

Views: 1326

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You want an aggregation with a case statement. The following query checks for multiple values (assuming no NULLs):

select (case when count(distinct Reference) = 1 then 'TRUE'
             else 'FALSE'
        end)
from t

If you really need the multiple rows as well:

select (case when count(distinct Reference) = 1 and count(*) > 1 then 'TRUE'
             else 'FALSE'
        end)
from t

Upvotes: 2

Related Questions