CODEWITHSUNDEEP
sqloracle-database
johanisani
johanisani

Reputation: 35

How do I combine the counts?

How do I combine the "number of operations" and total amount spent to each patient ID ?

DESIRED OUTCOME EXAMPLE: Patient_ID = 112, NUMBER OF OPERATIONS = 4, Total amount spend = 29552

SELECT a.patient_id, COUNT(a.Patient_id) AS "Number of operations", (SUM(ot.theatre_fee + s.Charges+(w.Daily_charge * ot.Days_in))) AS "Total amount spend"
    FROM person p , admission a , staff s , operation_type ot, Ward w
    WHERE s.person_id = p.person_id
    AND a.patient_id = p.person_id
    AND a.expected_op = ot.op_code
    GROUP BY ot.theatre_fee, s.Charges, a.Patient_id, w.Daily_charge, ot.Days_in;

An image of the output. Please click on it to enlarge

Upvotes: 1

Views: 42

Answers (3)

joserobertog
joserobertog

Reputation: 119

In the GROUP BY line should be only the field "a.Patient_id", the other fields

GROUP BY a.Patient_id

Upvotes: 1

eshirvana
eshirvana

Reputation: 24603

then you just need to group by Patient_id column only:

SELECT a.patient_id
    , COUNT(a.Patient_id) AS "Number of operations"
    , (SUM(ot.theatre_fee + s.Charges+(w.Daily_charge * ot.Days_in))) AS "Total amount spend"
FROM person p , admission a , staff s , operation_type ot, Ward w
WHERE s.person_id = p.person_id
  AND a.patient_id = p.person_id
  AND a.expected_op = ot.op_code
GROUP BY a.Patient_id

Upvotes: 1

Achraf Ben Soltane
Achraf Ben Soltane

Reputation: 304

You should only group by patient_id.

SELECT a.patient_id, COUNT(a.Patient_id) AS "Number of operations", (SUM(ot.theatre_fee + s.Charges+(w.Daily_charge * ot.Days_in))) AS "Total amount spend"
FROM person p , admission a , staff s , operation_type ot, Ward w
WHERE s.person_id = p.person_id
AND a.patient_id = p.person_id
AND a.expected_op = ot.op_code
GROUP BY a.Patient_id;

Upvotes: 1

Related Questions