Finding Usage Overlap

Hello EE,

I have a set of usage data that I need to group by unique usage.  For example in my data, I need to know how many customer are contacting the help desk by using phone and mail format. In my data I can have customer who are using both mail and phone, phone only or mail only.  In the attached example, I need to group my data like below

Mail_Only        Phone_Only      Phone_Mail
     21                    2                      3

The data is in the data tab

Thanks in advance for your help
testdata.xlsx
LVL 1
fb1990Asked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
This query gave me the results you were looking for.

SELECT SUM(CASE 
             WHEN phone_cnt = 0 
                  AND mail_cnt > 0 THEN 1 
             ELSE 0 
           END) mail_only, 
       SUM(CASE 
             WHEN phone_cnt > 0 
                  AND mail_cnt = 0 THEN 1 
             ELSE 0 
           END) phone_only, 
       SUM(CASE 
             WHEN phone_cnt > 0 
                  AND mail_cnt > 0 THEN 1 
             ELSE 0 
           END) phone_mail 
FROM   (SELECT pool_id, 
               SUM(CASE device 
                     WHEN 'Phone' THEN 1 
                     ELSE 0 
                   END) phone_cnt, 
               SUM(CASE device 
                     WHEN 'Mail' THEN 1 
                     ELSE 0 
                   END) mail_cnt 
        FROM   tbl1 
        GROUP  BY pool_id) cnt_data; 

Open in new window

0
 
fb1990Author Commented:
Sweet!  Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.