So I have this query that I am using for a time tracking system. Basically it adds up time spent grouped by day, for each employee.
SELECT hours.empid, hours.clockin, SUM(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))) as timecalc, employees.employeeid, employees.firstname, employees.lastname, employees.class FROM hours, employees WHERE hours.empid = employees.employeeid AND hours.clockin BETWEEN '$_POST[startdate]' AND '$_POST[enddate]' GROUP BY DATE(hours.clockin), hours.empid ORDER BY hours.empid, hours.clockin DESC
This works great, I can see how many hours each employee worked for each day between a time period I specify. But then I have to manually add up each employees total time.
How can I write this query to have it loop through each employee, do what is being done above but then sum the total hours in a last row? I am aware that there is a ROLLUP function that somewhat does this, but visually it is just confusing, because there is no way to style or separate the rollup row from the next group, and it pulls random fields from the row above it... it's just visually not clear.