This is my code and everything is correct excepted the average temperature since the results are shown that everyone got the same temperature. exp:student a got 4 times observation of temperature whereas student b only got 1 time of observation. so how can I deal with it?
SELECT
patient_id AS "Patient ID",
CONCAT(CONCAT(first_name,' '),surname) AS "Patient Name" ,
admission_date AS "Date Admitted",
discharge_date AS "Date Discharged",
ROUND(AVG(observ_value),2) AS "Average Temperature",
daily_charge AS "Expenses"
FROM person,ward,admission,observation
WHERE person.person_id = admission.patient_id
AND admission.admission_id NOT IN (SELECT operation.admission_id FROM operation)
AND ward.ward_code = admission.ward_code
AND observ_type = 'Temp'
AND discharge_date IS NOT NULL
GROUP BY patient_id, admission_date, discharge_date, daily_charge, first_name, surname
ORDER BY admission_date
Advertisement
Answer
This is essentially a complete answer. It shows that writing the SQL more clearly, and with the suggested JOIN / ON form, can more clearly point out when join criteria is missing:
Test case (The new schema and some simple data)
Fiddle to the full test case with full data and both queries for comparison
Updated to allow for patients who have no temperature observations
SELECT patient_id AS "Patient ID"
, CONCAT(CONCAT(first_name,' '),surname) AS "Patient Name"
, admission_date AS "Date Admitted"
, discharge_date AS "Date Discharged"
, ROUND(AVG(observ_value),2) AS "Average Temperature"
, daily_charge AS "Expenses"
FROM person
JOIN admission
ON person.person_id = admission.patient_id
AND admission.admission_id NOT IN (SELECT operation.admission_id FROM operation)
AND discharge_date IS NOT NULL
JOIN ward
ON ward.ward_code = admission.ward_code
LEFT JOIN observation
ON observ_type = 'Temp'
AND observation.admission_id = admission.admission_id
GROUP BY patient_id, admission_date, discharge_date, daily_charge, first_name, surname
ORDER BY admission_date
;
Notice the missing join criteria in the ON clause related to the observation table. Just specifying the observ_type is not sufficient.
Result with sample data added and with the corrected join criteria:
Old result with the same data and missing join criteria:
Here’s the partial old query result (from the question) with the full data from the OP
Here’s the new result with full data, with the proposed solution above:
This allows patients which have no temperature observations.
Note: There could be other problems in the logic that the question does not ask about. The missing join criteria of the original SQL certainly does create a mess. This is now cleaned up.
Here’s the kind of detail the question should provide, along with some usable test data that produces the result you don’t expect:
alter session set NLS_DATE_FORMAT='DD/MM/YYYY'; CREATE TABLE PERSON ( Person_id NUMBER(3) PRIMARY KEY, Surname VARCHAR2(20), First_name VARCHAR2(20), Sex CHAR(1), Birth_date DATE, Street VARCHAR2(40), Town CHAR(20), Postcode NUMBER(4), Next_of_kin NUMBER(3) ); CREATE TABLE STAFF ( Person_id NUMBER(3) PRIMARY KEY, Start_date DATE, Staff_type VARCHAR2(15), Charges NUMBER(10,2), Resign_date DATE, FOREIGN KEY (Person_id) references PERSON(Person_id) ); CREATE TABLE WARD ( Ward_code CHAR(3) PRIMARY KEY, Ward_name VARCHAR2(20), Bed_count NUMBER(4), Opened_date DATE, Last_painted_date DATE, Daily_charge NUMBER(10,2) ); CREATE TABLE OPERATION_TYPE ( Op_code CHAR(3) PRIMARY KEY, Operation_name VARCHAR2(50), Theatre_fee NUMBER(10,2), Days_in NUMBER(5) ); CREATE TABLE ADMISSION ( Admission_id NUMBER(3) PRIMARY KEY, Patient_id NUMBER(3), Admission_date DATE NOT NULL, Expected_op CHAR(3), Admitted_by NUMBER(3), Ward_code CHAR(3), Discharge_date DATE, FOREIGN KEY (Patient_id) references PERSON(Person_id), FOREIGN KEY (Expected_op) references OPERATION_TYPE(Op_code), FOREIGN KEY (Admitted_by) references PERSON(Person_id), FOREIGN KEY (Ward_code) references WARD(Ward_code) ); CREATE TABLE OPERATION ( Operation_id NUMBER(3) PRIMARY KEY, Actual_op CHAR(3), Admission_id NUMBER(3), Op_date DATE, Surgeon NUMBER(3), Anaesthetist NUMBER(3), FOREIGN KEY (Surgeon) references PERSON(Person_id), FOREIGN KEY (Anaesthetist) references PERSON(Person_id), FOREIGN KEY (Actual_op) references OPERATION_TYPE(Op_code), FOREIGN KEY (Admission_id) references ADMISSION(Admission_id) ); CREATE TABLE OBSERVATION( Admission_id NUMBER(3), Observ_date DATE, Observ_time NUMBER(4), Observ_type CHAR(10), Observ_value NUMBER(4), Staff_id NUMBER(3), PRIMARY KEY (Admission_id,Observ_date,Observ_time,Observ_type), FOREIGN KEY (Admission_id) references ADMISSION(Admission_id), FOREIGN KEY (Staff_id) references STAFF(Person_id) ); -- With test data INSERT INTO person (Person_id, First_name, Surname) VALUES (1, 'First1', 'Last1'); INSERT INTO person (Person_id, First_name, Surname) VALUES (2, 'First2', 'Last2'); INSERT INTO ward (Ward_code, Daily_charge) VALUES (1, 100); INSERT INTO ward (Ward_code, Daily_charge) VALUES (2, 120); INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (1, 1, current_date, current_date, 1); INSERT INTO admission (admission_id, patient_id, admission_date, discharge_date, ward_code) VALUES (2, 2, current_date, current_date, 2); INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1000, 'Temp', current_date, 1000); INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (1, 1100, 'Temp', current_date, 1100); INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2000, 'Temp', current_date, 2000); INSERT INTO observation (admission_id, observ_value, observ_type, Observ_date, Observ_time) VALUES (2, 2100, 'Temp', current_date, 2100);