# [Solved] SQL querying – Doctors and Hospitals [closed]

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).

solved SQL querying – Doctors and Hospitals [closed]