CODEWITHSUNDEEP
sqlhadoophive
Rahul Prabhune
Rahul Prabhune

Reputation: 3

SQL aggregation to add new column with flag

I have a column with 

Country
-------
Canada
India
USA
India
China
Canada

I want to update this table by inserting another column called M_U which can only have 0 or 1

If country occurs more than once = 1 If country occurs only once = 0

output
-------
Canada 1
India 1
USA 0
India 1
China 0
Canada 1

Upvotes: 0

Views: 136

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You should use windows functions for this:

select t.*, 
       (case when count(*) over (partition by country) > 1 then 1 else 0 end) as flag
from t;

This is essential a_horse_with_no_name's answer. I'll delete it if that answer is undeleted.

Upvotes: 0

isaace
isaace

Reputation: 3429

This should work for you:

select a.country, case when b.c >1 then 1 else 0 end  
from countries a
join (
      select Country, count(*) as c from countries group by Country
) b on b.country = a.country

Upvotes: 1

Related Questions