write sql query in different way

Bot _Vvk 0
Viewed 23

how can i write this query in different ways:

SELECT COUNT(*) AS patient_count FROM 
(select * from patient_score GROUP BY patient_id, service_date_end) AS p
 JOIN map_cohort_patient AS m ON p.patient_id = m.patient_id 
WHERE cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54") 
AND service_date_end = "2021-07-31"
mysql sql
1 Answers

Since you only want a specific service_date_end, you should put that in the subquery, rather than getting all the dates and then filtering.

Since you only care about the patient_id, just select that and use DISTINCT to get rid of the duplicates.

SELECT COUNT(*) AS patient_count
FROM map_cohort_patient AS m
JOIN (
    SELECT DISTINCT patient_id
    FROM patient_score
    WHERE service_date_end = '2021-07-31'
) AS p ON p.patient_id = m.patient_id
WHERE m.cohort_id = UUID_TO_BIN_F("38f58f93-b80d-4a54")

If the subquery is slow, you should add an index on service_date_end so it doesn't require a full scan.

Barmar0
Related