Aggregation function with join operation

Hi every one ,i need help to run this query

SELECT encounters. PATIENT, encounters. sum (TOTAL_CLAIM_COST) AS TOTAL_COST ,
patients. FIRST_NAME,
patients. LAST_NAME

FROM patients
JOIN encounters
ON patients.Id = encounters.PATIENT
WHERE encounters . REASONDESCRIPTION = “COVID-19”
GROUP BY encounters. PATIENT

the two buckets are

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

and

Patients( Id , First ,LAST_NAME )

i can perform join when there is no grouping or aggregation function

SELECT d.total_cost, p.*
FROM ( SELECT e.patient, SUM(e.Total_Claim_Cost) AS totoal_cost
       FROM encounters AS e
       WHERE e.reasondescrition = "COVID-19";
       GROUP BY e.patient) AS d 
JOIN patients AS p
JOIN ON d.patient = p.id
1 Like

It gives me empty result

“results”:

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

Please post the both the documents. Json fields are case sensitive, It is hard to see what real field name.

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;