Aggregation function with join operation 2

SQL++
,
1

if i need to perform the following query
Number of female and male patient with covid
if the patient bucket has the following attributes
Patients( Id , First ,LAST_NAME , gender)
and

Encounters( Id ,Start date ,Stop ,Patient ,Organization,Provider,Payer ,Total_Claim_Cost , REASONDESCRIPTION )

what is wrong with the following query

SELECT d. COUNT(PATIENT) AS PATIENT_NUM ,

   p. GENDER

FROM (

SELECT encounters. PATIENT

FROM encounters

WHERE encounters.REASONDESCRIPTION ="COVID-19" ) AS d

JOIN patients AS p ON d.PATIENT = p.Id

GROUP BY p. GENDER
2 
INSERT INTO encounters VALUES (UUID(), {"patient": "p02234", "reasondescription":"COVID-19"});
INSERT INTO encounters VALUES (UUID(), {"patient": "p01234", "reasondescription":"COVID-19"});
INSERT INTO patients VALUES (UUID(), {"id": "p01234", "gender":"male"});
INSERT INTO patients VALUES (UUID(), {"id": "p02234", "gender":"female"});

CREATE INDEX ix1 ON encounters(reasondescription, patient);
CREATE INDEX ix2 ON patients(id, gender);

SELECT  p.gender, COUNT(1) AS count
FROM encounters AS e
JOIN patients AS p ON p.id = e.patient

WHERE e.reasondescription = "COVID-19"
GROUP BY p.gender;
1 Like