select DATE_FORMAT(o.obs_datetime, '%d-%m-%Y') AS 'Date',
       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 'Others',
       COUNT(o.value_coded)                    AS 'Total Patient',
       SUM(IF(o.value_coded in (select cv.concept_id
                                from concept_view cv
                                where concept_full_name in ('Ticket Type, Paid')), 1,
              0))                              AS 'Paid Patient',
       SUM(IF(o.value_coded in (select cv.concept_id
                                from concept_view cv
                                where concept_full_name in ('Ticket Type, Free')),
              1, 0))                           AS 'Free Patient',
       SUM(IF(o.value_coded in (select cv.concept_id
                                from concept_view cv
                                where concept_full_name in ('Ticket Type, Paid')), 1,
              0)) * 10                         AS 'Total TK.'
from obs o
         inner join encounter e on o.encounter_id = e.encounter_id
         inner join visit v on e.visit_id = v.visit_id
         inner join visit_type vt on v.visit_type_id = vt.visit_type_id
         inner join person p on p.person_id = o.person_id and p.voided = 0
where cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#'
  and o.voided = 0
  and o.concept_id in
      (select cv.concept_id from concept_view cv where concept_full_name = 'Ticket Type')
  and vt.name = 'outpatient'
group by cast(o.obs_datetime as date)
union all
select 'Total',
       sum(IF(p.gender = 'M', 1, 0)),
       sum(IF(p.gender = 'F', 1, 0)),
       sum(IF(p.gender = 'O', 1, 0)),
       COUNT(o.value_coded),
       SUM(IF(o.value_coded in (select cv.concept_id
                                from concept_view cv
                                where concept_full_name in ('Ticket Type, Paid')), 1, 0)),
       SUM(IF(o.value_coded in (select cv.concept_id
                                from concept_view cv
                                where concept_full_name in ('Ticket Type, Free')), 1, 0)),
       SUM(IF(o.value_coded in (select cv.concept_id
                                from concept_view cv
                                where concept_full_name in ('Ticket Type, Paid')), 1, 0)) * 10
from obs o
         inner join encounter e on o.encounter_id = e.encounter_id
         inner join visit v on e.visit_id = v.visit_id
         inner join visit_type vt on v.visit_type_id = vt.visit_type_id
         inner join person p on p.person_id = o.person_id and p.voided = 0
where cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#'
  and o.voided = 0
  and o.concept_id in
      (select cv.concept_id from concept_view cv where concept_full_name = 'Ticket Type')
  and vt.name = 'outpatient';