[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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
0
fb1990
Asked:
fb1990
1 Solution
 
johnsoneSenior 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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now