1

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? – mustaccio Feb 2 '15 at 16:35
1

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. – Atieh Feb 2 '15 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. – Delitisco Feb 2 '15 at 18:07
  • when you distinct on count(mentee_id), you get the # of mentees the mentor interacted with. – Atieh Feb 2 '15 at 18:13
  • Yes, you are right you can remove the distinct since that is unique anyways. – Delitisco Feb 2 '15 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? – Delitisco Feb 2 '15 at 18:21
0

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. – Atieh Feb 2 '15 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. – DeadZone Feb 3 '15 at 12:00
  • I don't need Mentors table in my case. But ya, that works.. – Atieh Feb 3 '15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.