I’ll help you with your first question, and I’ll leave to you the second.
- 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
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
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
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:
- You need to understand how to relate data stored in different tables. Study how to use
INNER JOIN(and also
- You need to understand how does
GROUP BYworks, and how to use aggregate functions (
- You know how to write subqueries. Now try to use them not only for
whereconditions, but as data sources (including them in
- 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]