How can I get hours worked totaled for a column?

I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough. Can someone lend a hand please 🙂 I have to finish this report and I know the answer to this will help me with many different scenarios as far as MYSQL Queries go. HELP

SELECT
    `ohrm_user`.`emp_number`, 
    `ohrm_attendance_record`.`punch_in_user_time`, 
     `ohrm_attendance_record`.`punch_out_user_time`, 
     `ohrm_user`.`user_name`, 
     time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60 AS time_to_sec_TIMEDIFF_punc, 
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S, 
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM 
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON 
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`, 
         `ohrm_attendance_record`.`punch_in_user_time`, 
         `ohrm_attendance_record`.`punch_out_user_time`, 
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`, 
         `ohrm_attendance_record`.`punch_in_user_time`

I cannot figure out how to calculate total hours for the user and date scope that is specified. The way it currently is the calculations only seem to total up each punch in and out in pairs. I need a total for the hours worked in the searched period. I am still learning about aggregates, sub queries and advanced normalization.

If you group by the punch in and out times you will ensure your aggregates do not take place at the user level. Take that out and add a sum around your timediff to aggregate the time worked. Also, Username, unless it is unique and corresponds to Emp Number may cause grouping issues.

SELECT
    `ohrm_user`.`emp_number`, 
    `ohrm_attendance_record`.`punch_in_user_time`, 
     `ohrm_attendance_record`.`punch_out_user_time`, 
     `ohrm_user`.`user_name`, 
     SUM(time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60) AS time_to_sec_TIMEDIFF_punc, 
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S, 
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM 
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON 
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`, 
         --REMOVE -`ohrm_attendance_record`.`punch_in_user_time`, 
         --REMOVE -`ohrm_attendance_record`.`punch_out_user_time`, 
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`, 
         `ohrm_attendance_record`.`punch_in_user_time`