CODEWITHSUNDEEP
phpmysql
Sharoon Amjid
Sharoon Amjid

Reputation: 617

How to calculate a percentage for all student that are present

There are two tables that are going to work together.

1st Table:

AttendanceID TeacherID BatchID SubjectID SemesterID Date 
          32       110       8         9          1 2016-08-04 
          31       102       8        10          1 2016-07-17 
          30       108       6        22          3 2016-06-27 
          29       109       7        18          2 2016-06-27 
          28       109       8        13          1 2016-06-27 
          27       110       7         7          2 2016-06-27 
          26       110       8         9          1 2016-06-27 
          25       104       2        42          7 2016-04-20 
          24       104       5        35          5 2016-04-14 
          23       104       2        42          7 2016-04-14
          22       102       2        41          7 2016-04-13 
          21       102       2        41          7 2016-04-10 
          20       102       6        23          3 2016-04-10 
          19       102       6        23          3 2016-04-10

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

Upvotes: 0

Views: 5473

Answers (3)

user6683818
user6683818

Reputation:

Try:

    SELECT  s.CollectID, s.Name, s.Gender, s.Photo, 
        (SELECT count(AttendanceID) from attendances WHERE SubjectID =? and BatchID = s.BatchID) as total,
        (SELECT count(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = s.CollectID and Status = 'present' and SubjectID = ?) as obtained
 FROM students s
 WHERE s.BatchID = ?

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31812

Put your queries in subselects:

SELECT
(SELECT COUNT(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = ? AND Status = 'present' AND SubjectID = ?) 
/ (SELECT COUNT(AttendanceID) FROM attendances WHERE SubjectID = ? AND BatchID = ?)
* 100

Or (more readable) join two subqueries:

SELECT (obtained * 100) / total
FROM (
    SELECT COUNT(AttendanceDetailID) AS total
    FROM attendancedetail
    WHERE CollegeID = ? AND Status = 'present' AND SubjectID = ?
) t
CROSS JOIN (
    SELECT COUNT(AttendanceID) AS obtained 
    FROM attendances
    WHERE SubjectID = ? AND BatchID = ?
) a

Upvotes: 1

Philipp
Philipp

Reputation: 2796

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.

Upvotes: 1

Related Questions