Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Finding Usage Overlap

Posted on 2014-04-10
2
Medium Priority
?
227 Views
Last Modified: 2014-04-10
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
Comment
Question by:fb1990
2 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 39993184
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
 
LVL 1

Author Closing Comment

by:fb1990
ID: 39993349
Sweet!  Thank you so much!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question