hello everyone , i have follwoing table structure
check_id(pk) | user_id(fk) | check_in_time( timestamp ) | check_out_time( timestamp ) | is_checked_out(0 or 1) | checked_by | family_id
now i want to calculate average of all checkIn and checkOut of user per day and then plot these data in graph this is what i have tried so far
SELECT DAYNAME(check_in_checkout_time.check_in_time) as a ,DAYNAME(check_in_checkout_time.check_out_time) as b , ROUND(AVG(MINUTE(TIMEDIFF( DATE_FORMAT(check_out_time, "%h:%i:%s") , DATE_FORMAT(check_in_time, "%h:%i:%s") ))),0) as timediff FROM check_in_checkout_time WHERE check_in_checkout_time.checked_by =?
this give result as a: wednesday b : wednesday timdiff: 14 and so on but does not give avgerage of all ?