CODEWITHSUNDEEP
sql
dgoodwin
dgoodwin

Reputation: 21

Case statement with contains

I wonder if you could help out with this case statement. I don't know if this can be done using a case statement or if a function needs doing.

So what I have is the below data:

Group Name   |      Address      |   Contact Name   |   Group_Type
MR A Haplin     4 Fox Close          Alfred Haplin     Current Tenant
MR D Yoti       4 Fox Close          David Yoti        Former Tenant
MRS S Pox       1 Drape Ave          Shelly Pox        Current Tenant

So for the above data, I have a current AND former tenant in 4 fox close, and just a current tenant in 1 Drape Ave.

I need some kind of Case Statement to show this:

Group Name   |      Address      |   Contact Name   |   Group_Type     |    CASE
MR A Haplin     4 Fox Close          Alfred Haplin     Current Tenant         1
MR D Yoti       4 Fox Close          David Yoti        Former Tenant          1 
MRS S Pox       1 Drape Ave          Shelly Pox        Current Tenant         2

So for any addresses that contain a former tenant AND current tenant, I need to show a 1, if just shows a current tenant without a former tenant, I need to show a 2.

Is there any case statement I can use for this, please? Or does a function need to be created?

Upvotes: 0

Views: 495

Answers (3)

India.Rocket
India.Rocket

Reputation: 1235

Used 2 CASE statements. One in inner query to create corresponding dummy values as cas to be able to use it in outer query to get the required flags

Try this:-

Select a.*, case when cas>2 then 1 else cas end as case
from
your_table_name a
inner join
(
Select Address, sum(case when Group_Type='Current Tenant' then 2 
                when  Group_Type='Former Tenant' then 3
                else 0 end) as cas
from
your_table_name
group by Address
) b
on a.Address=b.Address;

Upvotes: 0

Kapil
Kapil

Reputation: 987

Select t1.*,case when t2.count_address=1 Then 2 Else 1 end as Case_Column
from table1 t1
inner join
(
select address,count(*)as count_address from table1
group by address
)t2
on t1.address=t2.address

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

Something like this should work in most versions of SQL:

SELECT t1.*, t2.status
FROM yourTable t1
INNER JOIN
(
    SELECT Address,
           CASE WHEN COUNT(*) = 2 THEN 1 ELSE 2 END AS status
    FROM yourTable
    GROUP BY Address
) t2
    ON t1.Address = t2.Address

This would work reliably if every address were unique, i.e. two different groups or contacts don't happen to exist at the same address. But it would be better to group using some sort of primary key column, e.g. an ID.

If you are using a database which supports analytic functions, then you could try the following

SELECT *,
       CASE WHEN COUNT(*) OVER (PARTITION BY Address) = 2 THEN 1 ELSE 2 END AS status
FROM yourTable

Upvotes: 1

Related Questions