title: Joining calculated sum with specified daterange from one table to another
body: <p>I am trying to get calculated overtime in my wanted results for each user for a specific period(21st last month to 20th current month, or todays date if lower than 20)</p>
  <p>HOURS</p>
  <pre><code>id  | user_id |       starttime      |        endtime       |      created_at     |
  1   |    1    |  2018-05-10 05:00:00 |  2018-05-10 16:00:00 | 2018-07-10 16:30:53 | 
  2   |    1    |  2018-08-28 06:00:00 |  2018-08-28 14:30:00 | 2018-08-28 17:00:16 |
  3   |    2    |  2018-08-28 06:00:00 |  2018-08-28 18:00:00 | 2018-08-28 17:55:22 |
  3   |    3    |  2018-08-28 06:00:00 |  2018-08-28 12:00:00 | 2018-08-28 12:55:22 |
  3   |    3    |  2018-08-28 06:00:00 |  2018-08-28 12:00:00 | 2018-08-28 12:56:22 |
  </code></pre>
  <p>USERS</p>
  <pre><code>id | name |      email      |   phone   |      adress      |
  1  | jane | gmail@gmail.com | 12312312  | somestreet 12    |
  2  | john | yahoo@yahoo.com | 44433322  | anotherstreet 23 |
  3  | joe  | joe@email.com   | 12344432  | paradise 223     |
  </code></pre>
  <p>This is what i want to do - with the overtimetotal having overtime in seconds for specific period
  WANTED RESULT</p>
  <pre><code>id | name |      email      |   phone   |      adress      | overtimeTotal |
  1  | jane | gmail@gmail.com | 12312312  | somestreet 12    |    234000     |
  2  | john | yahoo@yahoo.com | 44433322  | anotherstreet 23 |     39240     |
  3  | joe  | joe@email.com   | 12344432  | paradise 223     |         0     |
  </code></pre>
  <p>I have been trying to do this for several hours now, looking at different solutions here on stack - but nothing seems to work for me.</p>
  <p>Basically this is the best i have been able to produce:</p>
  <pre><code>SELECT u.*, r.overTimeTotal from users u
  INNER JOIN 
  (
      SELECT user_id,
      SUM( 
          IF( TIMESTAMPDIFF(SECOND, starttime, endtime) &gt;= TIME_TO_SEC('07:24:00:0000')
             , TIMESTAMPDIFF(SECOND, starttime, endtime)  - TIME_TO_SEC('07:24:00:0000')
             , 0
            )  
      ) as overTimeTotal FROM hours
      WHERE starttime BETWEEN 2020-06-21 AND 2020-07-20
  ) r ON u.id
  </code></pre>
  <p>This almost does what i want, but it adds all the overtime for the period for all users and uses it in the overtimetotal field.</p>
  <pre><code>id | name |      email      |   phone   |      adress      | overtimeTotal |
  1  | jane | gmail@gmail.com | 12312312  | somestreet 12    |    273240     |
  2  | john | yahoo@yahoo.com | 44433322  | anotherstreet 23 |    273240     |
  3  | joe  | joe@email.com   | 12344432  | paradise 223     |    273240     |
  </code></pre>
  <p>What am i doing wrong? Can't wrap my head around it. I have really tried alot of things, LATERAL LEFT JOIN and so on.</p>
  <p>SOLUTION</p>
  <p>I finally managed to solve this with help from the comments</p>
  <pre><code>SELECT u.name, u.email,
  u.id,
  (
      SELECT 
          SUM(
              IF(
                  TIMESTAMPDIFF(SECOND, h.starttime, h.endtime) &gt;= TIME_TO_SEC('07:24:00:0000'),
                  TIMESTAMPDIFF(SECOND, h.starttime, h.endtime) - TIME_TO_SEC('07:24:00:0000'),
                  0
                )
             )
      FROM hours h
      WHERE DATE(h.starttime) BETWEEN '2020-06-21' AND '2020-07-20'
      AND h.user_id = u.id
  ) AS overtimeTotal
  FROM hours h
  INNER JOIN users u on h.user_id = u.id
  GROUP BY u.id
  </code></pre>