Sql-server – Calculating percentage from two tables

aggregatejoin;sql server

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

Best Answer

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
Related Question