select if(u.username <=> null, 'Total', u.username) AS 'User 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 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
         inner join users u on u.user_id = o.creator 
where cast(o.obs_datetime AS DATE) BETWEEN '#startDate#' AND '#endDate#'
  and o.voided = 0 AND o.creator NOT IN(6)
  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 u.username
with rollup;