ClassRoomBatchSudentAttendanceCountForDate

SELECT
  sclb.id AS batchId,
  MIN(ml.text) AS batchName,
  COUNT(bsa.batchStudent) AS totalStudent,
  COUNT(
    CASE
      WHEN attendanceStatus = 'ATTENDANCE_STATUS.PRESENT' THEN bsa.id
    END
  ) AS presentCount,
  COUNT(
    CASE
      WHEN attendanceStatus = 'ATTENDANCE_STATUS.ABSENT' THEN bsa.id
    END
  ) AS absentCount,
  bsa.subjectCode AS subject,
  bsa.date AS date,
  bsa.attendanceContext AS attendanceContext
FROM
  ltlddb.SchoolCourse sc
  JOIN ltlddb.sclevel scl on sc.id = scl.SchoolCourse
  JOIN ltlddb.sclevelbatch sclb on scl.id = sclb.sclevelid
  JOIN ltlddb.sclevelbatchmltnamem slbml on sclb.id = slbml.LTLDSCLevelBatch
  JOIN ltlddb.ltldmltext ml on slbml.MLText = ml.id
  JOIN ltlddb.batchstudent bs on sclb.id = bs.SCLevelBatch
  JOIN ltlddb.batchstudentattendance bsa on bs.id = bsa.batchstudent
WHERE
  sc.school =  <::SCHOOL_ID::>
  AND IF(
    Time(bsa.Date) = '18:30:00',
    DATE_ADD(bsa.Date, interval 330 MINUTE),
    DATE(bsa.Date)
  ) = <::FOR_DATE::>
  AND bsa.createdTenant = <::CURRENT_TENANT_ID::>
  AND sc.isActive = 1
  AND scl.isActive = 1
  AND sclb.isActive = 1
  AND bs.isActive = 1
  AND bsa.isActive = 1
GROUP BY
  batchId,
  subject,
  attendanceContext;