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

Question

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?

[Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) — philipxy, Sep 30 '22 at 18:46
@Shadow alright dude sorry i ruined the feng shui of stack overflow with my one incorrect tag — Emily, Sep 30 '22 at 19:03
Mysql and snowflake each implement sql differently. A code for one may not work for the other! — Shadow, Sep 30 '22 at 19:05

score 1 · Accepted Answer · answered Sep 30 '22 at 18:30

1

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

answered Sep 30 '22 at 18:30

Lukasz Szozda

162,964
23
234
275

1

Thank you Lukasz! I didn't know PARTITION BY was a clause in SQL – Emily Sep 30 '22 at 19:00

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

1 Answers1