Link to home
Start Free TrialLog in
Avatar of graziazi
graziaziFlag for Afghanistan

asked on

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
Avatar of tigin44
tigin44
Flag of Türkiye image


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 = ?
      
Avatar of graziazi

ASKER

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
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
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
ASKER CERTIFIED SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks