Databases
StackOverflow2013
Postsdbo
POMysql joining multiple totals - making query efficient
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql joining multiple totals - making query efficient
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USWill
    1. USRoman Ali
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMysql joining multiple totals - making query efficient
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMysql joining multiple totals - making query efficient
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload