Data:

As it can be seen, there is a record for the starting hour of a Subject and another for the finishing time of the same Subject (data comes like that). What I intend to know is the weekly amount of hours dedicated to classes by a specific teacher, let's say for "Albert".

Query:

$result = mysqli_query($link, "SELECT SUBTIME(max(Hour), min(Hour)) AS TeachTime, Subject FROM T1 WHERE Teachers LIKE '%Albert%' GROUP BY Subject ORDER BY Subject ASC") or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo $row['TeachTime']." Hrs. ";
}
}

Output:

Weekly teaching time: 02:00 Hrs. 02:00 Hrs. 01:30 Hrs. 01:30 Hrs.

Desired output:

Weekly teaching time: 07:00 Hrs.

As you can see, I don't know how to perform the addition of every resulting amount of hours. How can I achieve that?

I have also tried GROUP BY Teachers but results are weird... not the addition result.

CodePudding user response：

You know how to get this:

mysql> select teacher, day, min(hour), max(hour) 
from T1 where teacher = 'Albert' group by teacher, day;
 --------- ----------- ----------- ----------- 
| teacher | day       | min(hour) | max(hour) |
 --------- ----------- ----------- ----------- 
| Albert  | Wednesday | 10:00:00  | 12:00:00  |
| Albert  | Monday    | 08:30:00  | 10:30:00  |
| Albert  | Friday    | 11:00:00  | 14:30:00  |
 --------- ----------- ----------- ----------- 

And you can use TIMESTAMPDIFF() to calculate the minutes:

mysql> select teacher, day, 
timestampdiff(minute, min(hour), max(hour)) as minutes 
from T1 where teacher = 'Albert' group by teacher, day;
 --------- ----------- --------- 
| teacher | day       | minutes |
 --------- ----------- --------- 
| Albert  | Wednesday |     120 |
| Albert  | Monday    |     120 |
| Albert  | Friday    |     210 |
 --------- ----------- --------- 

Now wrap that as a subquery in another aggregation query:

mysql> select teacher, sum(minutes) as total_minutes 
from (
  select teacher, day, 
  timestampdiff(minute, min(hour), max(hour)) as minutes 
  from T1 where teacher = 'Albert' group by teacher, day) as t 
group by teacher;
 --------- --------------- 
| teacher | total_minutes |
 --------- --------------- 
| Albert  |           450 |
 --------- --------------- 

CodePudding user response：

This is the code I end up using thanks to the guidance of Bill Karwin

$result = mysqli_query($link, "SELECT Teacher, sum(TeachTime) AS WeekTime FROM (SELECT Teacher, Day, SUBTIME(max(Hour), min(Hour)) AS TeachTime FROM T1 WHERE Teacher LIKE '%Albert%' GROUP BY Teacher, Day) AS ProfTime GROUP BY Teacher") or die(mysqli_error($link));
if (mysqli_num_rows($result)!==0) {
echo "Weekly teaching time: ";
while($row = mysqli_fetch_array($result)){
echo $row['WeekTime']." Hrs. ";
}
}