title: SQL query for getting total hours worked and overtime in one query - resulting in resultset with data for all users
body: <p>I am trying to get total hours and total overtime for a given period of time, for all users. I have managed to build a query that will give the desired result, but for a specific user decided by id only.</p>
  
  <pre><code>SELECT 
      navn AS name, 
      hour AS hoursTotal, 
      overTime as overtimeTotal 
  FROM ( 
      SELECT SUM(TIMESTAMPDIFF(SECOND, starttid, sluttid)) AS hour
      FROM hours 
      WHERE hours.user_id = 14 
      AND DATE(starttid) BETWEEN '2018-8-25' AND '2018-9-25'
      ) AS T,
      (
      SELECT SUM( IF( TIMESTAMPDIFF(SECOND, starttid, sluttid) &gt;= TIME_TO_SEC('07:24:00:0000') , TIMESTAMPDIFF(SECOND, starttid, sluttid) - TIME_TO_SEC('07:24:00:0000') , 0 ) ) AS overTime
      FROM hours 
      WHERE user_id = 14 
      AND DATE(starttid) BETWEEN '2018-8-25' AND '2018-9-25' 
      ) AS O,
      (
      SELECT name AS navn 
      FROM users
      WHERE users.id = 14 
      ) AS F
  </code></pre>
  
  <p>This gives me:</p>
  
  <pre><code>name       | hoursTotal  | overtimeTotal
  John Doe   | 245700      | 85860
  </code></pre>
  
  <p>John Doe has user.id 14</p>
  
  <p>I cannot get my head around getting this result, but for all users in the period 2018-8-25 to 2018-9-25 </p>
  
  <p>Desired Result:</p>
  
  <pre><code>name       | hoursTotal  | overtimeTotal
  John Doe   | 245700      | 85860
  Jane Doe   | 354664      | 78243
  Tarzan     | 203111      | 0
  Batman     | 100233      | 0
  </code></pre>
  
  <p>I am hoping someone can help me in the right direction here.</p>