PHP store total count in a variable for each specific MySQL column value

Question

Im trying to create a Day Closure for our factory (employees).

What I have created so far:

• It show's a total of all records in the database with startdate and status as given into the SQL SELECT function. So $totalWorkedTime = 16.68. But this is not what I want. I want to show for each department the total value.

  $datestring = '23-10-2017';
  $qClosure = 'SELECT * FROM timeRegistration WHERE startdate="'. $datestring .'" && status="3" ';
  $rClosure = mysqli_query($conn, $qClosure);
  
  $totalWorkedTime = 0;
  
  while($row = mysqli_fetch_assoc($rClosure)) {
  
  $totalWorkedTime += $row['worktime'];
  
  }
  echo $totalWorkedTime;
  

My goal is as following:

• A function which stores for the different departments (1, 2, 3, 4, 5, 6) a total count of worked hours. In my MySQL Database I have a column called: worktime. In this column I have different values like: 3.42, 3.42, 4.92, 4.92 (these are hours). So I have 4 records right. Now these records have different departments which stored in the column: department.

I want that my function stores those values for each department. How can I manage this? An example is:

$totalhoursDepartment5 = 6.84; $totalhoursDepartment4 = 9.84;

The function should create more $totalhoursDepartment if is there any other records with department: 3 or 2 (example).

Answer 1

SQL sum() will do this for you:

 SELECT SUM(worktime),department
 FROM Depts
 GROUP BY department;

Department should be a foreing key to a department table.

Answer 2

    $datestring = '23-10-2017';
    $qClosure = 'SELECT SUM(worktime) AS totalworktime
FROM timeRegistration
Where departement in (select departement
from timeRegistration
WHERE startdate="'. $datestring .'"
and status="3")';
    $rClosure = mysqli_query($conn, $qClosure);

?

Use 'sum' and 'in'

http://sql.sh/cours/where/in and

http://sql.sh/fonctions/agregation/sum