Mysql – Summing Column values based on group by criteria

group byMySQLsum

So I have this query 

    select *, TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime) 
as hours from tbltimetracker
          LEFT JOIN tblcustomers 
on tbltimetracker.customer_id = tblcustomers.customer_id
          LEFT JOIN tblusers 
on tbltimetracker.user_id = tblusers.user_id 
          WHERE tbltimetracker.owner_id = 38
          LIMIT 0, 50'

My intent is to group these by customer and sum the hours based on that. So full disclosure is I have a client who wants to import their payroll and their payroll company needs the excel sheet in a certain format which is like this but the only difference is, employees (users) hours are totaled by the customers (or locations) that they worked at. So for instance, currently it's:

Payroll Id | User Name | Customer  | Hours |
5C           Tom         S. County   5
10C          Sam         N. County   3
5C           Tom         S. County   4
5C           Tom         N. County   7

But it should but 

Payroll Id | User Name | Customer  | Hours |
5C           Tom         S. County   9
10C          Sam         N. County   3
5C           Tom         N. County   7

This is what I've come up with

    select *, SUM(TIMEDIFF(tbltimetracker.tt_endtime, tbltimetracker.tt_begintime))
as hours from (select * from tbltimetracker where tbltimetracker.customer_id = tblcustomers.customer_id)
          LEFT JOIN tblcustomers 
on tbltimetracker.customer_id = tblcustomers.customer_id
          LEFT JOIN tblusers 
on tbltimetracker.user_id = tblusers.user_id 
          WHERE tbltimetracker.owner_id = 38
          LIMIT 0, 50'

But this does not work, I hope it gives you an idea though.

Best Answer

So to get the hours from the two timestamps as they were summed in the group by (note that tt_timeadjust is by default, in minutes) I used 

SELECT SQL_CALC_FOUND_ROWS *, ROUND((SUM( TIMESTAMPDIFF(MINUTE, tbltimetracker.tt_begintime, tbltimetracker.tt_endtime ) + tbltimetracker.tt_timeadjust)) / 60, 2) AS hours FROM tbltimetracker LEFT JOIN tblcustomers on tbltimetracker.customer_id = tblcustomers.customer_id LEFT JOIN tblusers on tbltimetracker.user_id = tblusers.user_id WHERE  tbltimetracker.owner_id = ?  GROUP BY tblcustomers.customer_id tblusers.user_id LIMIT 0, 50
Related Question