SQL SUM Fuction

[inspireddesign]

Hello All!!

 

I'm trying to SUM the total hours an employee has worked using the SQL statement below.  I can't seem to figure it out... The statement works but once I GROUP BY e.Id the HoursWorked output is lost.

 

The TIMEDIFF takes the datetime stamp of the field and outputs the data to HoursWorked.  I figured I could just take that array and SUM up the Hours for each employee.  But of course it tells me that the HoursWorked field doesn't exist.  Am I on the right track or can this even be done?  Thanks for any help on this one.

 

<?php

	  $sql = "SELECT *,
			  DATE_FORMAT( Clockin, '%H:%i' ) AS FormattedTimeIn,				  
			  DATE_FORMAT( Clockout, '%H:%i' ) AS FormattedTimeOut,				  

			  TIMEDIFF( Clockout, Clockin ) AS HoursWorked,
			  SUM(HoursWorked) AS sumHours

			  FROM time_clock AS tc
			  JOIN employees AS e ON e.Id = tc.Emp_id 
			  JOIN location AS loc ON loc.LocationID = tc.LocationID
			  JOIN employmentdetail AS ed ON ed.UserId = e.Id

			  WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
			   AND (" . $selLoc . ") AND ed.Active = 1 

			   GROUP BY e.Id  
			  ORDER BY e.LastName ASC";

	$listPayrollSum = mysql_query( $sql );	

?>

[kickstart]

Hi

 

Group by will result in fields that are not in the group by clause or not agregated fields having a dubious value. Ie, say you had a table of classes and selected a pupils name, the class id and the average grades for people in that class by using something like SELECT classId, Name, AVG(grade) FROM ClassTable GROUP BY classId, the name that is returned would be pretty random.

 

It looks like what you want is a list of each group of hours for each employee with an extra column for the total hours for that employee.

 

Something like this would do it:-

 

<?php

	  $sql = "SELECT *,
			  DATE_FORMAT( Clockin, '%H:%i' ) AS FormattedTimeIn,				  
			  DATE_FORMAT( Clockout, '%H:%i' ) AS FormattedTimeOut,				  
			  TIMEDIFF( Clockout, Clockin ) AS HoursWorked,
			  Deriv1.sumHours
			  FROM time_clock AS tc
			  JOIN employees AS e ON e.Id = tc.Emp_id 
			  JOIN location AS loc ON loc.LocationID = tc.LocationID
			  JOIN employmentdetail AS ed ON ed.UserId = e.Id
			  JOIN (SELECT Emp_id, SUM(HoursWorked) AS sumHours FROM time_clock AS clock WHERE clock.Clockin BETWEEN '".$Date1."' AND '".$Date2."' GROUP BY Emp_id) Deriv1 ON tv.Emp_id = Deriv1.Emp_id
			  WHERE tc.Clockin BETWEEN '".$Date1."' AND '".$Date2."' 
			   AND (" . $selLoc . ") AND ed.Active = 1 
			  ORDER BY e.LastName ASC";

	  $sql = "SELECT Emp_id, SUM(HoursWorked) AS sumHours FROM time_clock AS clock WHERE clock.Clockin BETWEEN '".$Date1."' AND '".$Date2."' GROUP BY Emp_id";

	$listPayrollSum = mysql_query( $sql );	

?>

 

However this will still require some work. I have no idea what $selLoc, so don't know if that is needed in the subselect.

 

All the best

 

Keith