CODEWITHSUNDEEP
sqloracle-database
Nik
Nik

Reputation: 175

Comparing values in two columns in same table and creating new column

This must be pretty easy query but for some reason I am not able to get the right query. I am using Oracle SQL. I have a table which has details of peoples who send gifts to each other, I want to track people who has exchanged the gift with eachother. I am trying to compare two columns in the same table.

Table:

SenderId  |  Sender    |  ReceiverId  |  Receiver  |
   1      |  Christen  |       30     |  Sharyn    |
   2      |  Will      |      5       |  Madeline  |
   7      |  Kim       |      25      |  Lee       |
  30      |  Sharyn    |       1      |  Christen  |
  25      |  Lee       |      7       |   Kim      |

Expected Result Table:

 Sender    |  Receiver  | Exchanged_Gift
 Christen  |  Sharyn    |      1
 Will      |  Madeline  |      0
 Kim       |  Lee       |      1

Since there are two groups of people who have exchanged the gift, the new column will have 1 if they exchanged the gift else 0.

1st Group --- Christen and Sharyn have both exchanged the gift.
2nd Group --- Kim and Lee have also exchanged the gift.

Upvotes: 0

Views: 1254

Answers (2)

Popeye
Popeye

Reputation: 35930

You can simply use the least and greatest function as follows:

Select case when count(1) = 1 then max(sender) else least(sender,receiver) end as sender, 
       case when count(1) = 1 then max(receiver) else greatest(sender,receiver) end as receiver,
       count(1) - 1 as exchanged_gifts
  From your_table
Group by least(sender,receiver), greatest(sender,receiver)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This is a little tricky because when there is only one row, then you want to keep the original ordering. One method is aggregation with union all:

select least(sender, receiver) as sender, greatest(sender, receiver) as receiver, 1 as exchanged
from t
group by least(sender, receiver), greatest(sender, receiver)
having count(*) = 2
union all
select sender, receiver, 0
from t
where not exists (select 1
                  from t t2
                  where t2.receiver = t.sender and t2.sender = t2.receiver
                 );

Upvotes: 1

Related Questions