CODEWITHSUNDEEP
sql
Müller
Müller

Reputation: 3

Check all values in another column (NULL or NOT NULL ) and return unique records

enter image description here

As you see for cid=1 all date fields are NULL and for cid=3 all date fields are NOT NULL.

I need get unique cids with new field, if all dates are NULL then with "NULL" and if all dates are NOT NULL then with "NOT NULL".

cid - new field
1 - NULL
3 - NOT NULL

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this with aggregation and case:

select cid,
       (case when count(datecol) = 0 then 'NULL'
             when count(datecol) = count(*) then 'NOT NULL'
        end) as newField
from t
group by cid
having count(datecol) in (0, count(*));

Upvotes: 2

Oto Shavadze
Oto Shavadze

Reputation: 42753

select cid, case when min(c) = 0 AND max(c) = 0 then 'null' when min(c) = 1 and max(c) = 1 then 'not null' end from (
    select cid, case when dt is null then 0 else 1 end as c from your_table
) t
group by cid
having min(c) = max(c)

Upvotes: 2

Related Questions