Aggregation function with join operation 2

vsr1 · June 28, 2021, 1:50am

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;