List the discharged patients who were earlier admitted but did not undergo any operation

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

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);