lundi 2 février 2015

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 can duplicates from the the subquery, since there are more records in TRANS.


Schema's


mentee_table



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


TRANS



|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





Aucun commentaire:

Enregistrer un commentaire