I have a table that logs calls and I'm trying to map the called numbers to the users answering them. To do this I need to display a table that shows me two seperate questions.

I guess the relevant columns in the existing table are "phone_number" and "user_email". Lets say we have around 400 numbers and 450 users.

Example Original Table, name would be "callsoftware_calls":

| User_Email | phone_number |
| -------- | -------- |
| [email protected]   | 1800 400   |
| [email protected]   | 1800 400   |
| [email protected]   | 1800 700   |
| [email protected]  | 1800 600  |
| [email protected]   | 1800 400   |
| [email protected]   | 1800 300   |

Ideal table outputs for the two queries would be:

| User | Count of assigned Numbers |
| -------- | -------- |
| [email protected]  | 8   |
| [email protected]   | 3   |

and

| PhoneNumber | Count of unique emails/users |
| -------- | -------- |
| 1800 400   | 10   |
| 1800 300   | 6   |

any ideas on how to generate these two tables?

Honestly I don't know where to start in generating such a query. I'm used to excel and just getting started with sql so trying to do basic analysis

CodePudding user response：

I'm checking your tables, and SQL allows us to count a field and grouping by another field.

--Example
WITH users AS (
    SELECT * FROM (
    VALUES
        (1, '[email protected]', '1800 400'), 
        (2, '[email protected]', '1800 400'), 
        (3, '[email protected]', '1800 700'), 
        (4, '[email protected]', '1800 600'), 
        (5, '[email protected]', '1800 400'), 
        (6, '[email protected]', '1800 300')
    
    ) AS _ (user_id, userEmail, phone)
)
--query that makes a grouping by userEmail and counts the phones 
--Qry count phone----------------------------------------------
select 
    userEmail, count(phone) as [Count of assigned Numbers] 
from 
    users
group by 
    userEmail
----------------------------------------------------------------

The query to count the users is very similar just change the fields

select 
    phone, count(userEmail) as [Count of unique emails/users] 
from 
    users
group by 
    phone

Best Regards

CodePudding user response：

If I understand the problem (the data does not seem to line up with the desired results, but I think that's just because it's abbreviated), this is a standard group by query. Group by one or the other column:

create or replace table T1 as 
select 
COLUMN1::string as User_Email,
COLUMN2::string as phone_number
from (values
('[email protected]','1800 400'),
('[email protected]','1800 400'),
('[email protected]','1800 700'),
('[email protected]','1800 600'),
('[email protected]','1800 400'),
('[email protected]','1800 300')
);

select User_Email, count(*) as ASSIGNED_NUMBERS from T1 group by User_Email;
select phone_number, count(*) as COUNT_OF_UNIQUE_EMAILS from T1 group by phone_number;

Output: