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

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

CodePudding user response：

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:

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