Re: Query w/sum and GROUP BY for related tables?

[Peter Brawley]

If the punches table is meant to track punching in and out, it'd be a lot more sensible to structure it as ...

punches(id, emp_id, t_in, t_out)

and put the logic to record the t_out value for a given t_in in a Trigger, so reporting would be simple and efficient ...

select emp_id, round( sum( time_to_sec(t_out) - time_to_sec(t_in) )/3600, 2 ) as Hours
from punches
where date(t_in) between ...
group by emp_id;