How to calculate the count of data that gets duplicated in a SQL join?-CodePudding

I'm having trouble figuring out what type of join to use or if a join is even the correct way to go about this. I have two tables:

Patients Table

ID   | month_joined
--------------------
A110 | jan 2013 
A111 | feb 2013
A112 | april 2013

Appointments Table

ID   | month_of_appt | number_of_appts
--------------------------------------
A110 | jan 2013      |       2
A110 | feb 2013      |       1
A111 | april 2013    |       3
A112 | dec 2013      |       1

I want to be able to see the count of patients who joined in a given month (count of month_joined from Patients Table) and the number of appointments for each month (number_of_appts from Appointments Table). When I use a left join, the output looks like this:

Patients & Appointments

ID   | month_joined | month_of_appt | number_of_appts
-----------------------------------------------------
A110 | jan 2013     | jan 2013      |       2
A110 | jan 2013     | feb 2013      |       1
A111 | feb 2013     | april 2013    |       3
A112 | april 2013   | dec 2013      |       1

So everything looks good except the month_joined column is duplicated for any patient that has had an appointment during more than one month, making it so if I want the count of the month_joined, it's bigger than it should be.

How do I go about calculating the accurate count of month_joined while still having the information on the month of each appointment and number of appointments per month?

CodePudding user response：

Based on the output it seems the tables are joined only on ID column. to get the number of rows "duplicated" from table patients windowed COUNT could be used:

SELECT *, COUNT(*) OVER(PARTITION BY p.ID, p.month_joined) AS number_of_appts
FROM Patients p
LEFT JOIN Appointments a
  ON p.ID = a.ID