Laravel join 3 tables

Hi,

Actually i need those 3 students details who were late to attend their classes more than others.

for example there are 20 students, but each student were late like:

a was 3 times late b was 5 times late c was 7 times late till 10th students. So i need just top 3 students detail who were late.

I was using the query which were showing this error:

    GROUP BY `Date`
)

[Err] 1111 - Invalid use of group function Query:

SELECT
    *
FROM
    student
WHERE
    ID IN
    (
        SELECT
            MAX( COUNT( SID ) )
        FROM
            attendance
        WHERE
            `Status`='Late'
        GROUP BY
            `Date`
    )

as i know i am using the max and count in a wrong way, please quid me how to use it or if someone write the correct query for me which helps me to get correct top 3 students records. thanks

tisuchi

6 years ago

Best Answer

Level 70

@ssuvin94

Try this?

SELECT
    student.*,
    lateAttendanceOccurrencesBySid.*
FROM
    (
        SELECT
            `sid`,
            COUNT(*) AS occurrences
        FROM
            attendance
        WHERE
            `status` = 'Late'
        GROUP BY
            `sid`
    ) AS lateAttendanceOccurrencesBySid

    INNER JOIN student ON student.id = lateAttendanceOccurrencesBySid.sid

ORDER BY
    lateAttendanceOccurrencesBySid.occurrences DESC

LIMIT
    3

1 like

Please or to participate in this conversation.