Get total count and total absents in one query

Question

I have 2 tables student and attendance. I need to join both tables and get the result like number of total absents and number of student in particular class and classname. I need a single query to get the below results

tbl_student:

admission_no(PK)          student_name        student_class
345                           John                 X A
352                           Sachin               X A
322                           Steve                IX A
123                           Pinky                X A
343                           Rose                 IX A

tbl_admission:

admission_no(FK)             date_absent
354                          2015-03-30
123                          2015-03-30
322                          2015-03-30

Result should be like this:

 Date_absent        total_absent        total_students      student_class
 2015-03-30            2                    3                  X A
 2015-03-30            1                    2                  IX A

Answer 1

you can do something like this to manage it in single query:

SELECT date_absent, total_absent, total_students, a1.student_class
FROM (
  SELECT date_absent, count( tbl_admission.admission_no ) AS total_absent, tbl_student.student_class
  FROM `tbl_admission`
  JOIN `tbl_student` ON tbl_student.admission_no = tbl_admission.admission_no
  GROUP BY tbl_student.student_class
)a1
JOIN (
  SELECT count( tbl_student.admission_no ) AS total_students, tbl_student.student_class
  FROM tbl_student
  GROUP BY tbl_student.student_class
)a2
ON a1.student_class = a2.student_class
ORDER BY `a1`.`student_class` DESC