one more question, what if I had other columns that cannot be used with an aggregate function (TEXT-based for example) but still needed to be paired with the first & last sign in? for instance, a randomly generated md5-ed ID by the sign in device that needs to be displayed as a reference:
Day EmployeeTotal-Hours First-Sign-In device-id-of-first-sign-in Last-Sign-In device-id-of-last-sign-in
20/4emp148:22 202cb962ac5.. 3:25 152d234b70..
On Thu, Apr 20, 2017 at 9:18 PM, Samed YILDIRIM <samed@reddoc.net> wrote:
Hi Muhannad,
Did you try using MIN and MAX function? I guess that following query solves your problem.
select employee_id, day, sum(total_hours) as total_hours, (select name from employee where id = employee_id) as emp_name, min(sign_in) as first_sign_in, max(sign_in) as last_sign_in
from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;