SQL querying – Doctors and Hospitals

sql

  1. Display doctorid, dname, total fees received by the doctor(s) who have treated more than one patient?
  2. Display hospitalid, hname, htype of hospital(s) which has the highest number of doctors associated with them.

Tables

table 1 – patient

patientid
pname
address
amount
ptype

table 2 – hospital

hospitalid
hname
htype

table 3 – doctor

doctorid
dname
specialization
hospitalid
status

table 4 – billing

billingid
patientid
doctorid
fees
billdate

So far this is what I have:

select
billing.doctorid,
sum (fees) as totalfees,
doctor.dname
from
billing, doctor
where
doctor.doctorid = billing.doctorid
group by
billing.doctorid,
doctor.dname
having
min ( billing.patientid ) <> max ( billing.patientid )

Best Solution

I'll help you with your first question, and I'll leave to you the second.

  1. Display doctorid, dname, total fees received by the doctor(s) who have treated more than one patient?

Let's split this problem in pieces:

So you need first to know which doctors have treated more than one patient. That information is in the table billing. So:

select doctorId, count(patientId) as patientCount
from (select distinct doctorId, patientId from billing) as a
group by doctorId
having count(patientId)>1;

This query will return only the Ids of the doctors that have more than one patient. Notice that I'm using a subquery to deduplicate the doctor-patient tuple.

Now let's attack the other part of this question: The total fees of each doctor. Again, that info is in the table billing:

select doctorId, sum(fees) as totalFees
from billing
group by doctorId;

Finally, let's put it all together, and include the doctor's info, which is in the table doctor:

select
    d.doctorId, d.doctorName, a.totalFees
from
    doctor as d
    inner join (
        select doctorId, sum(fees) as totalFees
        from billing
        group by doctorId
    ) as a on d.doctorId = a.doctorId
    inner join (
        select doctorId, count(patientId) as patientCount
        from (select distinct doctorId, patientId from billing) as a
        group by doctorId
        having count(patientId)>1;
    ) as b on d.doctorId = b.doctorId;

Hope this helps


Things you need to study and (or) keep in mind:

  1. You need to understand how to relate data stored in different tables. Study how to use INNER JOIN (and also LEFT JOIN and RIGHT JOIN)
  2. You need to understand how does GROUP BY works, and how to use aggregate functions (sum(), count(), etcetera).
  3. You know how to write subqueries. Now try to use them not only for where conditions, but as data sources (including them in from statements)
  4. Keep a copy of the reference manual of your RDBMS at hand. Also a good book on SQL can help you (go to a bookstore or library and find one you like).