School-Student-Reports.sql

#1 schools
select
  RM.rev_district_name,
  count(CASE WHEN SD.class_end IN (4, 5) and S.school_type='G' THEN SD.school_code END ) AS GovPrimarySchools,
  count(CASE WHEN SD.class_end IN (4, 5) and S.school_type='A' THEN SD.school_code END ) AS AidPrimarySchools,
  count(CASE WHEN SD.class_end IN (4, 5) and S.school_type='U' THEN SD.school_code END ) AS AidPrimarySchools,
  count(CASE WHEN SD.class_end =7 and S.school_type='G' THEN SD.school_code END ) AS GovUpperPrimarySchools,
  count(CASE WHEN SD.class_end =7 and S.school_type='A' THEN SD.school_code END ) AS AidUpperPrimarySchools,
  count(CASE WHEN SD.class_end =7 and S.school_type='U' THEN SD.school_code END ) AS AidUpperPrimarySchools,
  count(CASE WHEN SD.class_end IN (8, 9, 10) and S.school_type='G' THEN SD.school_code END ) AS GovSecondarySchools,
  count(CASE WHEN SD.class_end IN (8, 9, 10) and S.school_type='A' THEN SD.school_code END ) AS AidSecondarySchools,
  count(CASE WHEN SD.class_end IN (8, 9, 10) and S.school_type='U' THEN SD.school_code END ) AS AidSecondarySchools
  from schools as S
  join school_details as SD on SD.school_code=S.school_code
  join rev_district_master as RM on RM.rev_district_code=S.rev_district_code
  group by S.rev_district_code
  order by S.rev_district_code


#2 students count

SELECT RM.rev_district_name, U.standard,
  count(CASE WHEN U.standard=1 and S.gender='m' THEN S.student_code END ) AS Im,
  count(CASE WHEN U.standard=1 and S.gender='f' THEN S.student_code END ) AS 'If',

  count(CASE WHEN U.standard=2 and S.gender='m' THEN S.student_code END ) AS IIm,
  count(CASE WHEN U.standard=2 and S.gender='f' THEN S.student_code END ) AS IIf,

  count(CASE WHEN U.standard=3 and S.gender='m' THEN S.student_code END ) AS IIIm,
  count(CASE WHEN U.standard=3 and S.gender='f' THEN S.student_code END ) AS IIIf,

  count( CASE WHEN U.standard=4 and S.gender='m' THEN S.student_code END ) AS IVm,
  count( CASE WHEN U.standard=4 and S.gender='f' THEN S.student_code END ) AS IVf,

  count( CASE WHEN U.standard=5 and S.gender='m' THEN S.student_code END ) AS Vm,
  count( CASE WHEN U.standard=5 and S.gender='f' THEN S.student_code END ) AS Vf,

  count( CASE WHEN U.standard=6 and S.gender='m' THEN S.student_code END ) AS VIm,
  count( CASE WHEN U.standard=6 and S.gender='f' THEN S.student_code END ) AS VIf,

  count( CASE WHEN U.standard=7 and S.gender='m' THEN S.student_code END ) AS VIIm,
  count( CASE WHEN U.standard=7 and S.gender='f' THEN S.student_code END ) AS VIIf,

  count( CASE WHEN U.standard=8 and S.gender='m' THEN S.student_code END ) AS VIIIm,
  count( CASE WHEN U.standard=8 and S.gender='f' THEN S.student_code END ) AS VIIIf,

  count( CASE WHEN U.standard=9 and S.gender='m' THEN S.student_code END ) AS IXm,
  count( CASE WHEN U.standard=9 and S.gender='f' THEN S.student_code END ) AS IXf,

  count( CASE WHEN U.standard=10 and S.gender='m' THEN S.student_code END ) AS Xm,
  count( CASE WHEN U.standard=10 and S.gender='f' THEN S.student_code END ) AS Xf

  FROM students AS S
  JOIN tbl_uid AS U ON U.student_code = S.student_code
  JOIN schools AS SM ON SM.school_code = U.school_code
  JOIN rev_district_master as RM on RM.rev_district_code=SM.rev_district_code
  group by RM.rev_district_code
  order by RM.rev_district_code

#3 students count

SELECT RM.rev_district_name,
  count(CASE WHEN SD.class_end IN (4, 5) and SM.school_type='G' and S.gender='m' THEN S.student_code END ) AS GPrimaryMale,
  count(CASE WHEN SD.class_end IN (4, 5) and SM.school_type='G' and S.gender='f' THEN S.student_code END ) AS GPrimaryFemale,

  count(CASE WHEN SD.class_end IN (4, 5) and SM.school_type='A' and S.gender='m' THEN S.student_code END ) AS APrimaryMale,
  count(CASE WHEN SD.class_end IN (4, 5) and SM.school_type='A' and S.gender='f' THEN S.student_code END ) AS APrimaryFemale,

  count(CASE WHEN SD.class_end IN (4, 5) and SM.school_type='U' and S.gender='m' THEN S.student_code END ) AS UPrimaryMale,
  count(CASE WHEN SD.class_end IN (4, 5) and SM.school_type='U' and S.gender='f' THEN S.student_code END ) AS UPrimaryFemale,

  count(CASE WHEN SD.class_end =7 and SM.school_type='G' and S.gender='m' THEN S.student_code END ) AS GUPMale,
  count(CASE WHEN SD.class_end =7 and SM.school_type='G' and S.gender='f' THEN S.student_code END ) AS GUPfemale,

  count(CASE WHEN SD.class_end =7 and SM.school_type='A' and S.gender='m' THEN S.student_code END ) AS AUPMale,
  count(CASE WHEN SD.class_end =7 and SM.school_type='A' and S.gender='f' THEN S.student_code END ) AS AUPfemale,

  count(CASE WHEN SD.class_end =7 and SM.school_type='U' and S.gender='m' THEN S.student_code END ) AS UAUPMale,
  count(CASE WHEN SD.class_end =7 and SM.school_type='U' and S.gender='f' THEN S.student_code END ) AS UAUPfemale,


  count(CASE WHEN SD.class_end IN (8, 9, 10) and SM.school_type='G' and S.gender='m' THEN S.student_code END ) AS GSecondaryM,
  count(CASE WHEN SD.class_end IN (8, 9, 10) and SM.school_type='G' and S.gender='f' THEN S.student_code END ) AS GSecondaryF,

  count(CASE WHEN SD.class_end IN (8, 9, 10) and SM.school_type='A' and S.gender='m' THEN S.student_code END ) AS ASecondaryM,
  count(CASE WHEN SD.class_end IN (8, 9, 10) and SM.school_type='A' and S.gender='f' THEN S.student_code END ) AS ASecondaryF,

  count(CASE WHEN SD.class_end IN (8, 9, 10) and SM.school_type='U' and S.gender='m' THEN S.student_code END ) AS USecondaryM,
  count(CASE WHEN SD.class_end IN (8, 9, 10) and SM.school_type='U' and S.gender='f' THEN S.student_code END ) AS USecondaryF


  FROM students AS S
  JOIN tbl_uid AS U ON U.student_code = S.student_code
  JOIN schools AS SM ON SM.school_code = U.school_code
  JOIN school_details as SD on SM.school_code=U.school_code
  JOIN rev_district_master as RM on RM.rev_district_code=SM.rev_district_code
  group by RM.rev_district_code
  order by RM.rev_district_code