Optimize SQL query multiple joins with count tables

Question

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.

It would help if you explain your business logic. What are 1xlate and 2xlate supposed to mean? As it is you're expecting us to figure out what you want to do by reading your code. This is hard. Thanks. — O. Jones
– O. Jones, Commented Jun 13, 2012 at 12:00
ok, basically the table contains data of number of students that are late. I have edited the above — d123
– d123, Commented Jun 13, 2012 at 12:04
what does it mean to be "late?" I still can't tell what you're trying to do here. (Please excuse me for being picky, but I have found that when I really understand what my query is supposed to produced, that it is easier to optimize it.) — O. Jones
– O. Jones, Commented Jun 13, 2012 at 12:06
I have added a sample data to the table above. basically, this system is for a school to record student who attends school late, any one who come in late will have their data recorded in the table above. — d123
– d123, Commented Jun 13, 2012 at 12:13

Peter Lang · Accepted Answer · 2012-06-13 12:34:02Z

3

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;

edited Jun 13, 2012 at 12:34

answered Jun 13, 2012 at 12:07

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d123 Over a year ago

Hi Peter,Thank for your answer, however my 1xLate and 2xLate are not table of its own, there are generated by the sub queries

Peter Lang Over a year ago

Have you tried the query? I am also generating those columns.

Collectives™ on Stack Overflow

Optimize SQL query multiple joins with count tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related