Find average amount paid to each doctor

Question

Create Table Doctor(
    doctorID varchar(50) Primary key,
    doctorFName varchar(50),
);

Create Table Appointment(
    appID varchar(50) Primary Key,
    doctorID varchar(50) Foreign Key References Doctor(doctorID),
);

Create Table Payment(
    paymentID varchar(50) Primary Key,
    paymentAmount int,
    appID varchar(50) Foreign Key References Appointment(appID),
);

paymentAmount is also known as payment for each appointment

How can I get the average payment amount of each doctor?

I tried:

SELECT d.doctorID, d.doctorFName, SUM(p.paymentAmount) as AverageDailySalary 
FROM Payment p JOIN Appointment a ON p.appID = a.appID JOIN Doctor d ON a.doctorID = d.doctorID 
ORDER BY d.doctorID

Can you please also share with us what you have tried so far? — Giorgos Betsos, Sep 06 '16 at 06:32
*What you have tried so far* should also included actual (and expected) results. — Jan Doggen, Sep 06 '16 at 08:18

score 0 · Answer 1 · answered Sep 06 '16 at 06:34

0

That's a simple JOIN..GROUP BY query :

SELECT d.doctorID,d.doctorFName,AVG(p.paymentAmount) as avg_pay
FROM Doctor d
JOIN Appoitment a
 ON(d.doctorID = a.doctorID)
JOIN payment p
 ON(a.appID = p.appID)
GROUP BY d.doctorID,d.doctorFName

answered Sep 06 '16 at 06:34

sagi

40,026
6
59
84

I'm not sure what this reveals about the salary of each patient – Strawberry Sep 06 '16 at 06:40
I'm not sure where he has hidden the information about patients :X . – sagi Sep 06 '16 at 06:42
Do you want me to include the patient details also, cause i made the tables simpler for you to understand – Niveth Sep 06 '16 at 07:04
Thank you VERY much SAGI it works, i had the JOIN functions confused – Niveth Sep 06 '16 at 07:05

Find average amount paid to each doctor

1 Answers1