count distinct if a condition is satisfied

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

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;