具有％百分比操作的嵌套count（）函数

I’m designing a program for my school to keep student attendance records. So far I have the following query working fine and now I would like to add an IF statement to perform a percentage operation when a certain condition is given. As it is, the query is using INNER JOIN to search for data from two different tables (oxadmain and stuattend) and it’s displaying the results well on a results table:

SELECT o.name 
     , o.year 
     , o.photoID 
     , o.thumbs 
     , s.ID 
     , s.studid 
     , s.date 
     , s.teacher
     , s.subject 
     , s.attendance    
  FROM stuattend s
  JOIN oxadmain o
    ON s.studid = o.stuid
 ORDER 
    BY name ASC

Now I would like to add an “if” statement that
1) finds when stuattend.attendance is = Absent, calculates the percentage of absences the students may have in any given period of time, and then stores that (%) value in “percentage” and
2) ELSE assigns the value of 100% to “Percentage”.

So far I’ve been trying with the following:

<?php $_GET['studentID'] = $_row_RepeatedRS['WADAstuattend']; ?>

SELECT oxadmain.name , oxadmain.year , oxadmain.photoID , oxadmain.thumbs , stuattend.ID , stuattend.studid , stuattend.date , stuattend.teacher, stuattend.subject , stuattend.attendance
CASE
WHEN stuattend.attendance = Absent THEN SELECT Count (studentID) AS ClassDays, (SELECT Count(*) FROM stuattend WHERE studentID = stuattend.studid AND Absent = 1) AS ClassAbsent, ROUND ((ClassAbsent/ClassDays)*100, 2) AS Percentage
ELSE
Percentage = 100
END
FROM stuattend INNER JOIN oxadmain  ON stuattend.studid=oxadmain.stuid
ORDER BY name ASC

Any suggestions on how to do this well?

Thank you for your attention

The base idea would be:

select stuattend.studid, sum(stuattend.attendance = `absent`) / count(*)
from stuattend
group by stuaddend.studid;

This very much depends on exactly one entry per student and per day, and of course gets 0 if no absence and 1 if always absent.

To make this a bit more stable I would suggest to write a calendar day table, which simply keeps a list of all days and a column if this is a school day, so workday=1 means they should have been there and workday=0 means sunday or holiday. Then you could left join from this table to the presence and absence days, and even would give good results when presence is not contained in your table.

Just ask if you decide which way to go.