Join 3 Tables with disparate counts

I have three tables:

• WellnessProviders
• WellnessVisits
• WellnessReferrals

Which look like this (sample):

| ProviderName | ProviderEmail   |
|--------------|-----------------|
| Steve        | steve@steve.com |
| Jeff         | jeff@jeff.com   |

And:

| ReferralNumber | AssignedToEmail |
|----------------|-----------------|
| 1              | steve@steve.com |
| 2              | jeff@jeff.com   |
| 3              | jeff@jeff.com   |

And:

| VisitNumber | CheckOutProviderEmail |
|-------------|-----------------------|
| 1           | steve@steve.com       |
| 2           | steve@steve.com       |

We are trying to create a query that counts the total referrals by provider AND the total visits by provider. If I write the queries separately, things work as expected:

SELECT wp.ProviderName, count(wr.AssignedToEmail)
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessReferrals wr ON wp.ProviderEmail=wr.AssignedToEmail
GROUP BY wp.ProviderName

Returns all providers by name and a count of all referrals.

SELECT wp.ProviderName, count(wv.CheckInProviderEmail)
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessVisits wv ON wp.ProviderEmail=wv.CheckOutProviderEmail
GROUP BY wp.ProviderName;

Returns all providers by name and a count of all visits. I am trying to combine the two sets so we get the provider's name, the count of referrals, and the count of visits. I tried to do this by writing

SELECT wp.ProviderName, count(wr.AssignedToEmail), count(wv.CheckOutProviderEmail)
FROM dbo.WellnessProviders wp
LEFT JOIN dbo.WellnessReferrals wr
    ON wp.ProviderEmail=wr.AssignedToEmail
LEFT JOIN dbo.WellnessVisits wv
    ON wv.CheckOutProviderEmail=wp.ProviderEmail
GROUP BY wp.ProviderName;

And while that produced all of the providers, the counts were all wrong. I need help joining the first two queries into a single table.

The desired result would be this:

| ProviderName | Total Referrals | Total Check-Outs |
|--------------|-----------------|------------------|
| Jeff         | 2               | 0                |
| Steve        | 1               | 2                |

In SQL joins multiply the number of rows, so if attempting to count (or any aggregation) you always run the risk of the aggregates being incorrect when performed over joins, especially over multiple joins. The simple solution to this is to do the aggregations as subqueries and join those together into a single query e.g:

SELECT
      wp.ProviderName
	, wr.count_assigned
	, wv.count_visits
FROM dbo.WellnessProviders wp
LEFT JOIN (
	SELECT
          AssignedToEmail
		, count(*) count_assigned
	FROM dbo.WellnessReferrals
	GROUP BY AssignedToEmail
	) wr ON wp.ProviderEmail = wr.AssignedToEmail
LEFT JOIN (
	SELECT
          CheckOutProviderEmail
		, count(*) AS count_visits
	FROM dbo.WellnessVisits
	GROUP BY CheckOutProviderEmail
	) wv ON wv.CheckOutProviderEmail = wp.ProviderEmail

You can use this code as:

SELECT ProviderName,
       COUNT(DISTINCT wr.AssignedToEmail) AS TotalReferrals,
       COUNT(DISTINCT wv.CheckOutProviderEmail) AS TotalCheckOuts
FROM WellnessProviders wp
LEFT JOIN WellnessReferrals wr ON wp.ProviderEmail = wr.AssignedToEmail
LEFT JOIN WellnessVisits wv ON wp.ProviderEmail = wv.CheckOutProviderEmail
GROUP BY ProviderName;

I have used here distinct keyword to ensure each referral and visit is counted once.

I hope this will help you!!