I have a table which shows if a address_no has a telephone or not. To determine, i am looking at cell_phone and house_phone columns and want to write ‘no phone’ only when house_phone and cell_phone is null.
every address_no has records for the people living at the address. If at least one person has a phone or house_phone is not null then that address_no has phone, otherwise address_no has no phone information.
ADDRESS_NO------PERSON_ID------CELL_PHONE-------HOUSE_PHONE 11111-----------11-------------111000---------------------- 11111-----------12-------------122000---------------------- 11111-----------13----------------------------------1313000 22222-----------21----------------------------------2121000 33333-----------31----------------------------------------- 33333-----------32----------------------------------------- 44444-----------41-------------411000---------------4141000 55555-----------51-------------511000---------------------- 55555-----------52----------------------------------------- 55555-----------53-----------------------------------------
As shown above, i want to have a result of 4 addresses out of 5 which have telephone info.
How can i write a sql query to find the number in oracle sql
Advertisement
Answer
You can use aggregation and a case expression:
select address_id,
(case when max(cell_phone) is null and max(house_phone) is null
then 'No Phone' else 'Phone'
end) as phone_flag
from t
group by address_id;