MySQL Count frequency of records

Table:

laterecords
-----------
studentid - varchar
latetime - datetime
reason - varchar

students
--------
studentid - varchar -- Primary
class - varchar

I would like to do a query to show the following:

Sample Report

Class      No of Students late  1 times  2 times   3 times  4 times    5 & more 
Class A        3                  1       0         2         0         0
Class B        1                  0       1         0         0         0

My query below can show the first column results:

SELECT count(Distinct studentid), class FROM laterecords, students
WHERE students.studenid=laterecords.studentid AND
GROUP BY class

I can only think of getting the results for each column and store them into php arrays. Then echo them to table in HTML.

Is there any better SQL way to do the above? How to do up the mysql query ?

Answers


Try this:

SELECT
    a.class,
    COUNT(b.studentid) AS 'No of Students late',
    SUM(b.onetime) AS '1 times',
    SUM(b.twotime) AS '2 times',
    SUM(b.threetime) AS '3 times',
    SUM(b.fourtime) AS '4 times',
    SUM(b.fiveormore) AS '5 & more'
FROM
    students a
LEFT JOIN
    (
        SELECT
            aa.studentid,
            IF(COUNT(*) = 1, 1, 0) AS onetime,
            IF(COUNT(*) = 2, 1, 0) AS twotime,
            IF(COUNT(*) = 3, 1, 0) AS threetime,
            IF(COUNT(*) = 4, 1, 0) AS fourtime,
            IF(COUNT(*) >= 5, 1, 0) AS fiveormore
        FROM 
            students aa
        INNER JOIN
            laterecords bb ON aa.studentid = bb.studentid
        GROUP BY 
            aa.studentid
    ) b ON a.studentid = b.studentid
GROUP BY
    a.class

How about :

SELECT numlates, `class`, count(numlates)
FROM
  (SELECT count(laterecords.studentid) AS numlates, `class`, laterecords.studentid
   FROM laterecords,
        students
   WHERE students.studentid=laterecords.studentid
   GROUP BY laterecords.studentid, `class`) aliastbl
GROUP BY `class`, numlates

Need Your Help

type="number" min and max attributes

html5

why does type="number" not work for me, also I get a problem with min and max attributes.

Sort order Dictionary with Arrays iPad 2 vs iPad Air 2. Why reversed?

ios arrays swift ipad air

I had a failing test on iPad Air 2 but it worked on iPad 2 and I couldn't figure it out.