CODEWITHSUNDEEP
mysqlselectcount
ak85
ak85

Reputation: 4264

More efficient way to count groups of data in mysql?

I have a SELECT statement for a list of students at a school, It currently runs with the below code, however I find it takes a good 1-2 seconds per count, most other simple queries on this database take less than half a second so I think I am doing this quite inefficiently here. Is there a better way to do this? 1-2 seconds might not sound like much but in a more detailed statement I add to the below to also count subjects present/absent etc so when I have everything included in the statement it is taking close to 30 seconds to run.

        SELECT studentID AS sID, stu.name AS Name,
        (
        SELECT COUNT( * )
        FROM attendance
        WHERE ((sID = 1)
        AND classdate < CURDATE( ))
        ) AS present, 
        (
        SELECT COUNT( * )
        FROM attendance
        WHERE ((sID != 1)
        AND classdate < CURDATE( ))
        ) AS absent
        FROM attendance
        INNER JOIN students AS stu ON attendance.studentID = stu.id
        WHERE awayid != -1
        GROUP BY sID
        ORDER BY present ASC

        ---------------------------------------------
        |   sID |   Name    |   present |   absent  |
        |   1   |   John    |   28      |   2       |
        |   2   |   Richard |   26      |   4       |
        |   3   |   Harry   |   22      |   8       |
        |   4   |   Sarah   |   22      |   8       |
        |   5   |   Tanya   |   20      |   10      |
        |   6   |   Nathan  |   20      |   10      |
        |   7   |   Kate    |   20      |   10      |
        ---------------------------------------------

Upvotes: 0

Views: 213

Answers (2)

Bohemian
Bohemian

Reputation: 424983

Making a few assumptions, try this:

SELECT
    studentID AS sID,
    stu.name AS Name,
    sum(a.sID = 1) as present,
    sum(a.sID != 1) as absent
FROM students AS stu
JOIN attendance a ON a.studentID = stu.id
    AND classdate < CURDATE( )
WHERE awayid != -1
GROUP BY 1, 2
ORDER BY present ASC

Note that in mysql true is 1 and false is 0, so SUM(condition) counts how many times it's true, thus avoiding the cumbersome use of CASE which wold be required by other databases.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use conditional aggregation:

select studentID as sID, stu.name,
       sum((sID = 1) AND classdate < CURDATE( )) as present,
       sum((sID != 1) AND classdate < CURDATE( )) as absent
FROM attendance INNER JOIN
     students AS stu
     ON attendance.studentID = stu.id
WHERE awayid != -1
GROUP BY sID
ORDER BY present ASC;

What confuses me, though, is the use of sID in the subquery. It would not refer to the studentId in the outer select. I suspect that another variable should be used there. (And, if there is an sid in attendance, why are you calling studentID sID in the outer query?)

Upvotes: 1

Related Questions