graziazi
asked on
Help needed to formulate a query
Hi. Please could someone help me formulate a query to find all invoices for a patient.
Ideally I would also like to be able to get the sum of fees 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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
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 = ?