CODEWITHSUNDEEP
mysqlsql
Vijay Vj
Vijay Vj

Reputation: 67

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 ?

Upvotes: 2

Views: 2718

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

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

Upvotes: 1

Lee
Lee

Reputation: 10603

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

Upvotes: 0

Related Questions