Sharoon Amjid Sharoon Amjid - 3 months ago 70
MySQL Question

How to calculate a percentage for all student that are present

There are two tables that are going to work together.

1st Table:

enter image description here

2nd Table:

enter image description here

The first table is consist of unique rows which will be going to use for each new attendance list in table two.

For example: In the first table, the

AttendanceID
21
is used in second table for Complete attendance of specific subject.

I want to calculate the percentage of all students in second table of a specific
Subject
and the total number can be get by the 1st table
AttendanceID


What I did in PHP is: First I get the total number from 1st table with this query:

SELECT COUNT(AttendanceID) FROM attendances WHERE SubjectID = ? AND BatchID = ?"


Once I get the total number of attendances of specific subject and batch from first table I store it in variable
$total
then I write another query for getting obtained attendance from second table:

SELECT COUNT(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = ? AND Status = 'present' AND SubjectID = ?"


After getting the obtained attendance I store it in variable
$obtained

Once I get both values then I calculate the percentage in PHP like this:

if(!empty($total) && !empty($obtained)) {
$result = (($obtained * 100)/ $total);
$result = round($result);
}


Here is the complete code of PHP:

public function showStateOfAttendance($subjectID, $batchID){

$st = $this->conn->prepare("SELECT CollegeID, Name, Gender, Photo FROM students WHERE BatchID = ?");
$st->bind_param("i", $batchID);
$st->execute();
$st->store_result();
$num_rows = $st->num_rows;
$st->bind_result($college_id, $name, $gender, $photo);


$this->response['attendance'] = array();
while($st->fetch()) {

$this->calcultaionOfAttendance($subjectID, $college_id, $name, $gender, $photo, $batchID);



}
return json_encode($this->response);
$st->free_result();
$st->close();

}


public function calcultaionOfAttendance($subjectID, $studentID, $name, $gender, $photo, $batchID) {

$stmt = $this->conn->prepare("SELECT COUNT(AttendanceID) FROM attendances WHERE SubjectID = ? AND BatchID = ?");
$stmt->bind_param("ii", $subjectID, $batchID);
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;
$stmt->bind_result($AttendanceID);
while($stmt->fetch()) {
$total = $AttendanceID;
}
$stmt->free_result();
$stmt->close();

$stmt2 = $this->conn->prepare("SELECT COUNT(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = ? AND Status = 'present' AND SubjectID = ?");
$stmt2->bind_param("ii", $studentID, $subjectID);
$stmt2->execute();
$stmt2->store_result();
$stmt2->bind_result($AttendanceDetailID);
while($stmt2->fetch()){
$obtained = $AttendanceDetailID;
}
if(!empty($total) && !empty($obtained)) {
$result = (($obtained * 100)/ $total);
$result = round($result);
$rating = ($result)/20;

$tmp = array();

$tmp['result'] = $result;
$tmp['total'] = $total;
$tmp['obtained'] = $obtained;
$tmp['rating'] = $rating;
$tmp['name'] = $name;
$tmp['college_id'] = $studentID;
$tmp['gender'] = $gender;
$tmp['photo'] = $photo;

array_push($this->response['attendance'],$tmp);



//var_dump(array($total, $obtained, $result, $rating, $studentID, $name));
}else if(empty($total)) {

$tmp = array();

$tmp['result'] = 0.0;
$tmp['total'] = 0.0;
$tmp['obtained'] = $obtained;
$tmp['rating'] = 0.0;
$tmp['name'] = $name;
$tmp['college_id'] = $studentID;
array_push($this->response['attendance'],$tmp);


//var_dump(array("0.0",$obtained, "0.0","0.0",$studentID,$name));
}else if(empty($obtained)) {

$tmp = array();

$tmp['result'] = 0.0;
$tmp['total'] = $total;
$tmp['obtained'] = 0.0;
$tmp['rating'] = 0.0;
$tmp['name'] = $name;
$tmp['college_id'] = $studentID;
array_push($this->response['attendance'],$tmp);


//var_dump(array($total, "0.0", "0.0","0.0", $studentID , $name));
}


}


Here is the android screen shot of the queries I did: The following result is for
SubjectID = 23
And
BatchID = 6


enter image description here

It get me the required result but I need better way to calculate this, is it possible to do this with single query?

Thanks

Answer
SELECT
   SUM(`Status` = 'present') AS presentCount,
   COUNT(*) AS totalCount,
   (SUM(`Status` = 'present') * 100) / COUNT(*) AS percent  
FROM
   details    
WHERE
   BatchID = 2 
   AND SubjectID = 41 
   AND CollegeID = 1214    
GROUP BY
   AttendanceID

Based on your example you don't even need to access the first table. You can just calculate it directly from the data in the second.

Comments