title: Mysql joining multiple totals - making query efficient
body: <p>I am creating a clock-in / clock-out system for employees.</p>
  
  <p>There is a tbl_clockins which contains records of each clock-in/clock-out session with information on whether each session is paid, how late the employee was for that session or how much overtime they did, etc.</p>
  
  <p>There is another table called tbl_user_work_settings where the manager can set which days employees are on holiday, or have taken off on sickness etc.</p>
  
  <p>I am creating some reports where I need totals for each employee, e.g. total days taken as holiday by each employee wihin a given date range. I have a very long query which actually gets all the required information, but it is huge and somewhat inefficient. Is there any way to make it smaller/more efficient? Any help is appreciated.</p>
  
  <pre><code>// get total days worked, unpaid days, bank holidays, holidays, sicknesses
  // and absences within given date range for given users            
  $sql = "SELECT us.username, daysWorked, secondsWorked,
              unpaidDays, bankHolidays, holidays, sicknesses, absences
  FROM
    (SELECT username FROM users WHERE clockin_valid='1') us
    LEFT JOIN (
      SELECT   username, selectedDate, count(isUnpaid) AS unpaidDays
      FROM     tbl_user_work_settings
      WHERE    isUnpaid = '1'
           AND selectedDate&gt;='$startDate'
           AND selectedDate&lt;='$endDate'
      GROUP BY username
    ) u ON us.username=u.username
    LEFT JOIN (
      SELECT   username, count(isBankHoliday) AS bankHolidays
      FROM     tbl_user_work_settings
      WHERE    isBankHoliday='1'
           AND selectedDate&gt;='$startDate'
           AND selectedDate&lt;='$endDate'
      GROUP BY username
    ) bh ON us.username=bh.username
    LEFT JOIN (
      SELECT   username, count(isHoliday) AS holidays
      FROM     tbl_user_work_settings
      WHERE    isHoliday='1'
           AND selectedDate&gt;='$startDate'
           AND selectedDate&lt;='$endDate'
      GROUP BY username
    ) h ON us.username=h.username
    LEFT JOIN (
      SELECT   username, count(isSickness) AS sicknesses
      FROM     tbl_user_work_settings
      WHERE    isSickness='1'
           AND selectedDate&gt;='$startDate'
           AND selectedDate&lt;='$endDate'
      GROUP BY username
    ) s ON us.username=s.username
    LEFT JOIN (
      SELECT   username, count(isOtherAbsence) AS absences
      FROM     tbl_user_work_settings
      WHERE    isOtherAbsence='1'
           AND selectedDate&gt;='$startDate'
           AND selectedDate&lt;='$endDate'
      GROUP BY username
    ) a ON us.username=a.username
    LEFT JOIN (
      SELECT   username, count(DISTINCT DATE(in_time)) AS daysWorked,
                  SUM(seconds_duration) AS secondsWorked
      FROM     tbl_clockins
      WHERE    DATE(in_time)&gt;='$startDate'
           AND DATE(in_time)&lt;='$endDate'
      GROUP BY username
    ) dw ON us.username=dw.username";
  
  if(count($selectedUsers)&gt;0)
    $sql .= " WHERE (us.username='"
         .  implode("' OR us.username='", $selectedUsers)."')";
  
  $sql .= " ORDER BY us.username ASC";
  </code></pre>