13 lines
675 B
MySQL
13 lines
675 B
MySQL
|
select if(l.name <=> null, 'Total', l.name) AS 'Room Name',
|
||
|
|
sum(IF(p.gender = 'M', 1, 0)) AS Male,
|
||
|
|
sum(IF(p.gender = 'F', 1, 0)) AS Female,
|
||
|
|
sum(IF(p.gender = 'O', 1, 0)) AS Other,
|
||
|
|
sum(IF(p.gender in ('F', 'M', 'O'), 1, 0)) AS Total
|
||
|
|
from obs o
|
||
|
|
INNER JOIN location l on o.value_complex = l.location_id
|
||
|
|
INNER JOIN person p on p.person_id = o.person_id
|
||
|
|
where cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#'
|
||
|
|
and o.voided = 0
|
||
|
|
and o.concept_id = (select cv.concept_id from concept_view cv where concept_full_name = 'Opd Consultation Room')
|
||
|
|
group by l.name
|
||
|
|
with rollup;
|