I want to do attendance aggregation in MySQL

What do you want to do

I want to get attendance, absences, lateness, and lateness (one absent out of three) from MySQL. I'd like to count the number of absences for being late (three times and one time) as one absence for being late every month.

What I don't know

I want to calculate attendance based on the following conditions, but I don't know how to write it

Attendance Requirements

If you attend all HR~4th class, your attendance rate will be 100%
91.6%
75% absent from one class or one class with HR
50% absent from HR and 2 classes or 2 classes
25% absent from HR and 3 classes or 3 classes
0% if all are absent or only HR is present

Thinking

"I created a query to aggregate attendance below, but I don't understand the description that incorporates the condition ""91.6% if HR is absent only"""

select
    students.name as "Students",
    klasses.name as "class",
    count(distinct lectures.id) as "Number of classes",
    count(recture_attend_logs.attend_status="attended" or null) as "attendance (number of classes),
    count(recture_attend_logs.attend_status="absented" or null) as "absent(number of classes),
    count(recture_attend_logs.attend_status="late" or null) as "Number of latencies",
    count(recture_attend_logs.attend_status="late" or null)DIV3 as "Late absent (one absent in three),
    count(recture_attend_logs.attend_status="public_absented"or null) as "missing (number of classes),
    count(recture_attend_logs.attend_status="closed" or null) as "School Closed",
    ROUND((count(lecture_attend_logs.attend_status = "attended" or null ) + (count(lecture_attend_logs.attend_status = "late" or null) - count(lecture_attend_logs.attend_status = "late" or null) DIV 3) + count(lecture_attend_logs.attend_status = "public_absented" or null) + count(lecture_attend_logs.attend_status = "closed"or null ))  / count(lecture_attend_logs.attend_status),5) * 100 as "Total Attendance (%)"
from
    lecture_attend_logs
    joinlectures onlecture_attend_logs.recture_id=lectures.id
    join students online_attend_logs.student_id=students.id
    join kclass_students on kclass_students.student_id=students.id
    join kclasses on klasses.id = kclass_students.kclass_id
where
    andrecture_attend_logs.deleted_at is null
group by students.name
order by lectures.id

texture_attend_logs table

students table

Lectures Table

kclasses table

kclass_students table

mysql

2022-09-30 11:27

1 Answers

This is the answer to your question what you don't understand.
Because HR is treated specifically in the calculation of attendance conditions, it will be conditional branching such as case statement.

What you want to doMonthly processing in one SQL can be too complicated to maintain, so we recommend using a temporary table or program to calculate the monthly processing.

The following is an example of SQL calculation of attendance.
Count HR attendance and regular class attendance in the with clause, and find attendance by conditional branching in the case statement of select.

The lectures table contains one day's worth of lectures and assumes that lectures.name='HR' is HR.
Use the with clause to run MySQL 8.0 or higher.

SQLDB Fiddle

with attend_count
as(select a.student_id,
            count(l.name='HR' and a.attend_status<>"absented" or null) ashp_count,
            count(l.name<>'HR' and a.attend_status<>"absented" or null) as normal_count
     from Lecture_attend_logsa
            join selectures on a.recture_id=l.id
     group by a.student_id)
select s.name,
       c.normal_count,
       case c.hp_count
       when 0 then case c.normal_count
                   when 4 then 91.6
                          elsec.normal_count*25
                   end
              elsec.normal_count*25
       end`Total attendance (%)`
from students
       join attach_count con s.id=c.student_id

DDL, etc.

create table students(
  id bigint(20),
  name varchar(10)
);

insert into students values (1, "Mr. A");
insert into students values(2, "Mr. B");
insert into students values (3, "Mr. C");

create table architectures(
  id bigint(20),
  name varchar(10)
);

insert entries values(0, "HR");
insert entries values (1, "Limited");
insert entries values(2, "2 limits");
insert entries values (3, "3 limits");
insert entries values (4, "4 limits");

create table texture_attend_logs(
  student_id bigint(20),
  texture_id bigint(20),
  attend_status varchar(255)
);

-- All Attendance (100%)
insert install_attend_logs values (1,0, "attended");
insert install_attend_logs values (1, 1, "attended");
insert install_attend_logs values (1, 2, "attended");
insert install_attend_logs values(1,3, "attended");
insert install_attend_logs values (1, 4, "attended");

-- HR and 1st and 2nd attendance (50%)
insert install_attend_logs values(2,0, "attended");
insert install_attend_logs values(2,1, "attended");
insert install_attend_logs values(2,2, "attended");
insert install_attend_logs values(2,3, "absented");
insert install_attend_logs values(2,4, "absented");

-- 1-4 Attendance (91.6%)
insert install_attend_logs values (3,0, "absented");
insert install_attend_logs values (3,1, "attended");
insert install_attend_logs values (3, 2, "attended");
insert install_attend_logs values(3,3, "attended");
insert install_attend_logs values(3,4, "attended");

Results

2022-09-30 11:27

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