CODEWITHSUNDEEP
sqloracle-database
Sandra
Sandra

Reputation: 9

SQL developer - Finding distinct phone numbers when 2 phone number columns exist

I need to get the count (in SQL developer) how many unique phone numbers customer has. Customer can have 2 different phone numbers (PHONE1 - mobile phone, PHONE2 - desk phone) and many different phone numbers in general registered for one customer (it depends on registration type).

I have tried different methods with CASE WHEN but no success. Do you have any ideas?

Thank you.

In the example below count should be 3.

CUSTOMER  PHONE1   PHONE2
abc       123456   123456
abc       123456
abc       654321   777777
abc       654321

In this case count should be equal to 3. As: 1st row - phone matches, then it is treated as 1 unique phone number. 2nd row - same as in the first row, still 1 unique phone number. 3rd row - phone1 and phone2 does not match, 2 unique numbers. 4th row - same as in the 3rd row, then in total 3 unique numbers.

Upvotes: 0

Views: 701

Answers (3)

Angel Sandin
Angel Sandin

Reputation: 1

Select
    COUNT(DISTINCT(phones)) 
FROM
(
    SELECT
        phone  AS phones 
    FROM
        customers 
    UNION ALL
    SELECT
        phone2 AS phones
    FROM
        customers
);

Upvotes: 0

Serg
Serg

Reputation: 22811

You can use LATERAL to unpivot phone numbers

WITH tbl(CUSTOMER,  PHONE1,   PHONE2) AS (
     SELECT'abc', '123456', '123456' FROM DUAL UNION ALL 
     SELECT'abc', '123456', NULL     FROM DUAL UNION ALL 
     SELECT'abc', '654321', '777777' FROM DUAL UNION ALL 
     SELECT'abc', '654321', NULL     FROM DUAL       
)
SELECT CUSTOMER, COUNT(DISTINCT p) cnt  
FROM tbl 
CROSS JOIN LATERAL (
    SELECT PHONE1 p FROM DUAL
    UNION
    SELECT PHONE2 FROM DUAL
  ) t
GROUP BY CUSTOMER

Upvotes: 1

Sergey
Sergey

Reputation: 5227

WITH CTE(CUSTOMER,  PHONE1,   PHONE2) AS
  (

     SELECT 'abc',       '123456',   '123456' UNION ALL 
     SELECT'abc' ,      '123456',    ' '      UNION ALL 
     SELECT'abc' ,      '654321' ,  '777777'  UNION ALL 
     SELECT'abc' ,      '654321',   ' '        
  )
  SELECT COUNT(DISTINCT PHONE1)FROM 
  (
    SELECT PHONE1 FROM CTE AS C
       UNION ALL 
    SELECT PHONE2 FROM CTE AS C2
 )X WHERE X.PHONE1<>' '

I guess, you can try something like above query. I replaced non-existing phones with empty string (' '), may be in your case it is NULL-value then you need to replace X.PHONE1<>' ' with X.PHONE1 IS NOT NULL

Upvotes: 0

Related Questions