Sharoon Amjid Sharoon Amjid - 1 year ago 168
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 Source
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download