# Optimize SQL query multiple joins with count tables

By any chance you would be able to help optimize this query without me showing you the tables?

My original table that all of these queries are derived from has the following columns and the table is named laterec-students

-------------------------------------------------------------- | studentid | name | class | latetime | waived | -------------------------------------------------------------- | ID1111STU | Stu 1 | 1A |2012-01-09 08:09:00 |Waived | SELECT A.class, NoStudentsLate, 1xLATE, 2xLATE FROM ( SELECT class, count(DISTINCT studentid) AS NoStudentsLate FROM `laterec-students` WHERE waived!="Waived" GROUP BY class ) AS A LEFT JOIN ( SELECT class, count(distinct studentid) AS 1xLATE from ( SELECT `laterec-students`.class, `laterec-students`.studentid FROM `laterec-students` WHERE waived!="Waived" GROUP BY studentid HAVING count(studentid)=1) as temp GROUP BY class ) AS B ON A.class=B.class LEFT JOIN ( SELECT class, count(distinct studentid) AS 2xLATE from ( SELECT `laterec-students`.class, `laterec-students`.studentid FROM `laterec-students` WHERE waived!="Waived" GROUP BY studentid HAVING count(studentid)=2) as temp GROUP BY class ) AS C ON A.class=C.class

This is what I am trying to accomplish

--------------------------------------------------------------------- | Class | Total # of students late | # late 1 times | # late 2 times | --------------------------------------------------------------------- | 1A | 5 | 3 | 2 | | 1B | 3 | 3 | 0 | ---------------------------------------------------------------------

So what this means, in class 1A, there are a total of 5 student late as counted using the student id. Out of this 5, 3 students are late once, and 2 students are late twice.

Again in class 1B, total 3 students are late, and all of them are only late once.

## Answers

I hope that I understood your query, but the following works with my SQL Fiddle example.

SELECT class, SUM(cnt > 0) AS NoStudentsLate, SUM(cnt = 1) AS 1xLate, SUM(cnt = 2) AS 2xLate FROM ( SELECT class, studentid, COUNT(*) AS cnt FROM `laterec-students` WHERE waived!='Waived' GROUP BY class, studentid ) t GROUP BY class;