excul attendances

select
       count(i.student_id) as jml_peserta,
       sum(
           case when i.attendance_status = true then 1 else 0 end
           ) as present,
       sum (
           case when i.attendance_status = false then 1 else 0 end
           ) as absent,
       i.date,
       d.academic_year,
       d.period_id,
       h.start_date,
       h.end_date
from tr_excul_registration
    join ms_student a
        on a.student_id = tr_excul_registration.student_id
            and a.active = true
            and a.deleted_at is null
    join ms_class_member b
        on b.student_id = a.student_id
            and b.deleted_at is null
    join ms_class c
        on c.class_id = b.class_id
            and c.deleted_at is null
    join tr_academic_calendar d
        on d.academic_calendar_id = c.academic_calendar_id
            and c.deleted_at is null
    join tr_school_level_relation e
        on e.school_level_relation_id = d.school_level_relation_id
            and e.deleted_at is null
    join ms_excul_mixed_year_level f
        on f.year_level_id = e.year_level_id
            and f.deleted_at is null
    join ms_school_level g
        on g.school_level_id = e.school_level_id
            and g.deleted_at is null
    join ms_excul h
        on h.excul_id = tr_excul_registration.excul_id
            and h.deleted_at is null
    join tr_excul_student_attendance i
        on i.student_id = a.student_id
            and i.excul_id = tr_excul_registration.excul_id
            and i.deleted_at is null
where tr_excul_registration.excul_id = 2
    and tr_excul_registration.deleted_at is null
group by
         i.date,
         d.academic_year,
         d.period_id,
         h.start_date,
         h.end_date