Help needed to formulate a query

Hi. Please could someone help me formulate a query to find all invoices for a patient.


I have a patient table with patient_id

I have an appointment table with appointment_id and patient_id

I have an invoice table with invoice_id and appointment_id and fee



Ideally I would also like to be able to get the sum of fees for a patient
graziaziAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
sory a copy past error.. I missed the last line while passing the query to the explorer... here is the correct one...

SELECT P.patient_id, SUM(I.fee)
FROM patient P
      INNER JOIN appointment A ON P.patient_id = A.patient_id
      INNER JOIN invoice I ON I.appointment_id = A.appointment_id
WHERE P.patient_id = ?
GROUP BY  P.patient_id    
0
 
tigin44Commented:

SELECT P.patient_id, SUM(I.fee)
FROM patient P
      INNER JOIN appointment A ON P.patient_id = A.patient_id
      INNER JOIN invoice I ON I.appointment_id = A.appointment_id
WHERE P.patient_id = ?
      
0
 
graziaziAuthor Commented:
Thank you. That is more or less what I want except for one thing. I get an error saying......

ORA-00937: not a single-group group function
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
knel1234Commented:
Select p.patient_id, TO_CHAR( SUM(i.fee), 9,999,999) BILL$
from patient p, appointment a, invoice i
where p.patient_id = a.patient_id
and a.appointment_id = i.appointment_id
group by a.patient_id
0
 
knel1234Commented:
Was this comment helpful? Yes No
Select p.patient_id, TO_CHAR( SUM(i.fee), 9,999,999) BILL$
from patient p, appointment a, invoice i
where p.patient_id = a.patient_id
and a.appointment_id = i.appointment_id
and p.patient_id = 1234 -- or some variable you create for your procq
group by a.patient_id
0
 
graziaziAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.