i have 2 tables
patient
| id | name | room |
|---|---|---|
| 1 | Adam | A |
| 2 | Ben | B |
| 3 | Charles | A |
| 4 | David | C |
| 5 | Eugen | B |
and table check:
| id | id_patient | check_date | condition |
|---|---|---|---|
| 1 | 1 | 2021-11-11 | bad |
| 2 | 2 | 2021-11-12 | good |
| 3 | 1 | 2021-11-17 | good |
| 4 | 3 | 2021-11-17 | poor |
| 5 | 4 | 2021-11-18 | bad |
| 6 | 5 | 2021-11-18 | bad |
| 7 | 3 | 2021-12-20 | poor |
| 8 | 4 | 2021-12-20 | bad |
| 9 | 5 | 2021-12-21 | bad |
| 7 | 1 | 2021-12-22 | bad |
| 8 | 2 | 2021-12-22 | poor |
| 9 | 3 | 2021-12-23 | good |
how the query to count patient by room from latest condition check date record .. Need result like this :
| room | bad | good | poor |
|---|---|---|---|
| A | 1 | 1 | 0 |
| B | 1 | 0 | 1 |
| C | 1 | 0 | 0 |
CodePudding user response：
This should work
select room,
sum(case when result = "bad" then 1 else 0 end) as bad,
sum(case when result = "good" then 1 else 0 end) as good,
sum(case when result = "poor" then 1 else 0 end) as poor
from
(select c3.id, patient, room, checkdate, result
from checks c3
inner join patients pt on pt.id = c3.patient
where c3.id in
(select id
from checks c1
where patient = c3.patient and
(select max(checkdate)
from checks c2
where c2.patient = c1.patient) = checkdate)) ch
group by room