Issue
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?
Solution
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 By - Lukasz Szozda Answer Checked By - Gilberto Lyons (PHPFixing Admin)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.