# Calculating percentage from two tables

I have two tables:

• First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table.
• Second one contains interactions of mentors with mentees on different dates, so mentos and mentees may appear multiple times.

I'm trying to create a join between those two tables where the result would be:

``````'mentor_id'|'# of people'|'# of distinct interactions'
``````

This way I would know whom did the mentor advice during a given period, against how many they are supposed to as %.

What I've done..

``````SELECT INTER.mentor_id, COUNT(DISTINCT INTER.mentee_id), COUNT(f.mentee_id)
FROM INTER WITH (NOLOCK)
INNER JOIN
(SELECT mt.mentee_id, mt.mentor_id
FROM mentee_table mt WITH (NOLOCK)
) as f
ON f.mentor_id = INTER.mentor_id
WHERE (//period)
GROUP BY INTER.mentor_id
``````

The problem with this is that when viewing the result without any groupings or aggregates, I receive duplicates from the subquery, since there are more records in INTER.

Schema's

mentee_table

``````|mentee_id|mentor_id
|1        |3
|2        |3
|3        |5
``````

INTER

``````|mentee_id|mentor_id
|1        |3
|1        |3
|1        |3
|2        |3
|3        |5
|3        |5
``````

In the end I will just use the counts to calculate percentage, such as

`COUNT(DISTINCT INTER.mentee_id)*100/COUNT(f.mentee_id)` which means the `INTER` table, when distinct, can only have <= # of people

Thanks

Edit

I ended up doing this query to also get mentors who never had any interactions. I had to use DISTINCT on both counts..

``````SELECT mt.mentor_id, COUNT(DISTINCT mt.mentee_id), COUNT(DISTINCT INTER.mentee_id)
FROM mentee_table mt WITH (NOLOCK)
LEFT JOIN INTER WITH (NOLOCK)
ON INTER.mentor_id = mt.mentor_id AND INTER.mentee_id = mt.mentee_id
GROUP BY mt.mentor_id
ORDER_BY mt.mentor_id
``````
• May be you want to join on `mentor_id` and `mentee_id` then? Feb 2, 2015 at 16:35

I see what your problem is you are duplicating the interactions because you are basically just trying to join those two tables together in your query. What you want to do is a correlated subquery to get the answer of how many interactions each mentor has had.

Something like this.

``````select a.mentor_id, count(distinct(a.mentee_id)) as UniqueMenteeCt,b.InterCt
from mentee_table a inner join
(select a.mentor_id, count(a.mentee_id) as InterCt
from INTER a
group by a.mentor_id
)b on a.mentor_id = b.mentor_id
group by a.mentor_id,InterCt
``````
• this can't solve my problem. there is no point of having distinct on `a.mentee_id` since it is already unique. The INTER table must have distinct on it in order to serve the table entry as a mentee, against the total mentees in the mentee_table when you group by mentor_id. Feb 2, 2015 at 17:47
• Based off of your table schema I get mentor 3 has 2 mentees and has seen them 4 times... I might be missing something but thats the results I get with my query. Feb 2, 2015 at 18:07
• when you distinct on `count(mentee_id)`, you get the # of mentees the mentor interacted with. Feb 2, 2015 at 18:13
• Yes, you are right you can remove the distinct since that is unique anyways. Feb 2, 2015 at 18:17
• I am rereading your question. Are you expecting to get 100% out of these examples assuming they are within the same time period? Feb 2, 2015 at 18:21

To get this result...

``````'mentor_id'|'# of people'|'# of distinct interactions'
``````

I think this is what you need...

``````WITH X as (
SELECT mentor_id, count(*) AS [Num_of_mentees] FROM mentee_table GROUP BY mentor_id
),
Y as (
SELECT mentor_id, count(*) AS [Num_of_interactions] FROM inter GROUP BY mentor_id
)
SELECT DISTINCT(m.mentor_id) -- Ideally you should use a Mentors table instead of this.
, X.Num_of_mentees
, Y.Num_of_interactions
FROM mentee_table m
JOIN X on X.mentor_id = m.mentor_id
JOIN Y on Y.mentor_id = m.mentor_id
``````

Here is the link to the SQL Fiddle.

• This is the closest to what I wanted, I added DISTINCT to my second COUNT, and, your query produced more results. Either way, I decided to do something else..look at my edit. Feb 2, 2015 at 21:00
• If you want mentors who have mentees but do not have any interactions, you could make the INNER JOIN on Y a LEFT JOIN. If you want mentors who do not currently have any mentees, then you should include the 'Mentors' table in your query and LEFT JOIN to the other two tables. Feb 3, 2015 at 12:00
• I don't need Mentors table in my case. But ya, that works.. Feb 3, 2015 at 13:55