Viewing Attendance by Student

What do you want to do

I want to get attendance per student on MySQL

Query Conditions

Attendance, absenteeism, lateness, and absenteeism (one absence out of three) are obtained in the SELECT statement. If you're late 3 times a month, count 1 time a month (e.g., if you're late 2 times in July or 2 times in August, you'll be late 4 times in total, so you'll be late 1 time, but you'll actually be over the moon, so I'd like to count 0 times)

Things to worry about

I want to display the total attendance rate when I specify the date.
According to the current method of writing, attendance rates are given every month, but not the total.
If you narrow down the following conditions, attendance rates for July and August will be released, but the total attendance rate for two months will not be released

where finish_time in 2021-07-01 and 2021-08-31)

Query

select
    selectures.finish_time as "Period",
    students.name as "Students",
    count(attend_logs.attend_status="attended"or null)as"attendance(number of classes),
    count(attend_logs.attend_status="absented" or null) as "absent (number of classes),
    count(attend_logs.attend_status="late" or null) as "Number of latencies",
    count(attend_logs.attend_status="late" or null)DIV3 as "missing due to lateness (one absence in three),
    Round(count(attend_logs.attend_status="attended" or null)+(count(attend_logs.attend_status="late" or null)-count(attend_logs.attend_status="late" or null)DIV3)+count(attend_logs.attend_status="late"or null)V
from
    attach_logs
    join 
    students 
    on 
    attend_logs.student_id=students.id
join
     Lectures
     on
     lectures.id=attend_logs.recture_id
where
     selectures.finish_time in (2021/05/01 and 2021/06/30)
group by students.name, date_format(lectures.finish_time, '%Y%M')

attach_logs table

students table

Lectures Table

mysql

2022-09-29 22:53

1 Answers

I think you should make the query in the questionnaire subquery and group by in student.

create table students(
    id bigint primary key,
    name varchar(10) unique not null
);

create table architectures(
    id bigint primary key,
    name varchar(10),
    finish_time datetime not null
);

create table attend_logs(
    id bigint primary key,
    student_id bigint not null,
    lecture_id bigint not null,
    attend_status varchar(255) not null,
    foreign key idx_student(student_id) references students(id),
    foreign key idx_recture(recture_id) references selectures(id)
);

insert into students (id, name) values
(1, 'alice'), (2, 'bob');

insert intractures(id, name, finish_time) values
(1, 'lec-0501', 'May 1, 2021'), (2, 'lec-0515', 'May 15, 2021'), (3, 'lec-0530', 'May 30, 2021'), (4, 'lec-0620', 'June 20, 2021');

insert into attend_logs(id, student_id, texture_id, attend_status)
(1, 1, 1, 'late'), (2, 1, 2, 'late'), (3, 1, 3, 'absented'), (4, 1, 4, 'late'),
(5, 2, 1, 'late'), (6, 2, 2, 'late'), (7, 2, 3, 'late'), (8, 2, 4, 'late')
;

Query questionnaire (equivalent) for tables

select
    date_format(lectures.finish_time, '%Y%M') as 'period',
    students.name as 'student',
    count(attend_logs.attend_status='attended' or null)as`attendance(number of classes),
    count(attend_logs.attend_status = 'absented' or null) as `absent (number of classes)',
    count(attend_logs.attend_status='late' or null) as `Number of late',
    count(attend_logs.attend_status='late' or null)DIV3 as `missing due to lateness (one absence in three)`,
    count(attend_logs.attend_status) as `Number of classes',
    (count(attend_logs.attend_status='attended' or null)+(count(attend_logs.attend_status='late' or null)-count(attend_logs.attend_status='late' or null)DIV3)+count(attend_logs.attend_status='late'or null)DIV3)+count(attend_logs.attend_status+null c'current_status)
    ROUND((count(attend_logs.attend_status = 'attended' or null ) + (count(attend_logs.attend_status = 'late' or null) - count(attend_logs.attend_status = 'late' or null) DIV 3) + count(attend_logs.attend_status = 'public_absented' or null) + count(attend_logs.attend_status = 'closed' or null ))  / count(attend_logs.attend_status),5) * 100 as `出席率合計(%)`
from
    attach_logs
    join 
    students 
    on 
    attend_logs.student_id=students.id
join
     Lectures
     on
     lectures.id=attend_logs.recture_id
where
     selectures.finish_time between 'May 1, 2021' and 'June 30, 2021'
group by students.name, date_format(lectures.finish_time, '%Y%M')
;

running

will be obtained, but with this query as a subquery

select sum(by_month.`attendance(by_month.`attendance(by_month.`attendance(by_month.`attendance(by_month.`attendance(by_month.`attendance(by_month.`attendance(by_month.`attendance)`attendance(by_month.`attendance(by_month.`)`attendance(by_month.`)`attendance(attendance(attendance(by_month.`)`)`)`)`)`attendance)`attendance
from(

    /* Query the previous question (equivalent) */
    select
        date_format(lectures.finish_time, '%Y%M') as 'period',
        students.name as 'student',
        count(attend_logs.attend_status='attended' or null)as`attendance(number of classes),
        count(attend_logs.attend_status = 'absented' or null) as `absent (number of classes)',
        count(attend_logs.attend_status='late' or null) as `Number of late',
        count(attend_logs.attend_status='late' or null)DIV3 as `missing due to lateness (one absence in three)`,
        count(attend_logs.attend_status) as `Number of classes',
        (count(attend_logs.attend_status='attended' or null)+(count(attend_logs.attend_status='late' or null)-count(attend_logs.attend_status='late' or null)DIV3)+count(attend_logs.attend_status='late'or null)DIV3)+count(attend_logs.attend_status+null c'current_status)
        ROUND((count(attend_logs.attend_status = 'attended' or null ) + (count(attend_logs.attend_status = 'late' or null) - count(attend_logs.attend_status = 'late' or null) DIV 3) + count(attend_logs.attend_status = 'public_absented' or null) + count(attend_logs.attend_status = 'closed' or null ))  / count(attend_logs.attend_status),5) * 100 as `出席率合計(%)`
    from
        attach_logs
        join 
        students 
        on 
        attend_logs.student_id=students.id
    join
        Lectures
        on
        lectures.id=attend_logs.recture_id
    where
        selectures.finish_time between 'May 1, 2021' and 'June 30, 2021'
    group by students.name, date_format(lectures.finish_time, '%Y%M')
    /* Query of the previous questionnaire */


by_month
group by `student';

If so,

is obtained.
(Alice is absent x1 and late x3, but 3 times late is not considered absent because they are over the moon.)

2022-09-29 22:53

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656