• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
graziazi
Asked:
graziazi
  • 2
  • 2
  • 2
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
tigin44Commented:
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
 
graziaziAuthor Commented:
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now