在MYSQL中联接具有SUM问题的表

Question

我一直在获取连接表上的SUM时总是遇到麻烦，总是有一个问题，我可以通过运行两个查询来获得所需的结果，我想知道这两个查询是否可以组合在一起以构成一个连接查询，这是我查询的结果有和我尝试加入查询

查询1

SELECT last_name, first_name, DATE_FORMAT( (mil_date),  '%m/%d/%y' ) AS dates, 

SUM( drive_time ) MINUTES FROM bhds_mileage LEFT JOIN bhds_teachers i 

ON i.ds_id = bhds_mileage.ds_id 

WHERE mil_date BETWEEN  '2016-04-11' AND  '2016-04-30'

AND bhds_mileage.ds_id =5

GROUP BY CONCAT( YEAR( mil_date ) ,  '/', WEEK( mil_date ) ) ,    

bhds_mileage.ds_id

ORDER BY last_name ASC , dates ASC 

以分钟为单位的输出是271、281、279

查询2

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,   

SUM(tm_hours) total FROM bhds_timecard LEFT JOIN bhds_teachers i 

ON i.ds_id = bhds_timecard.ds_id 

WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5

GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 

ORDER BY last_name ASC, dates ASC

这里的输出是33.00，36.00，26.75

现在我尝试加入查询

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,  

SUM(tm_hours) total,  SUM( drive_time ) MINUTES FROM bhds_timecard 

LEFT JOIN bhds_teachers i ON i.ds_id = bhds_timecard.ds_id 

LEFT JOIN bhds_mileage ON DATE_FORMAT((bhds_timecard.tm_date), '%m/%d/%y') = 

DATE_FORMAT((bhds_mileage.mil_date), '%m/%d/%y') AND bhds_timecard.ds_id = bhds_mileage.ds_id

WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5

GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id 

括号是预期的

这输出1044（271），1086（281），1215（279）