Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Oct 2010
    Thanked 1 Time in 1 Post

    Nested query, then SUM

    Good day all,
    I need some assistance on how to finish this query.

    SELECT hours.empid, SUM(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))) as timecalc, employees.employeeid, employees.firstname, employees.lastname, employees.class FROM hours, employees WHERE hours.empid = employees.employeeid AND hours.clockin BETWEEN '$_POST[startdate]' AND '$_POST[enddate]' GROUP BY hours.empid ORDER BY hours.empid DESC
    Above I am summing clock in and clout out times in a timeclock system. This works great, but I would also like to show each clock in and clock out record before displaying the sum for that employee.

    Can I loop through each employees records, display them, and then display the summed results at the end, then onto the next employee and so on?

    I hope that makes sense!

  2. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    That would be far the more sensible way.

    Something like this (not real code, but should give you the idea):

    $sql = "SELECT  E.empid, E.firstname, E.lastname, E.class, H.clockout, H.clockin
    FROM employees AS E HOURS AS H 
    WHERE H.empid = E.employeeid 
    AND H.clockin BETWEEN '$_POST[startdate]' AND '$_POST[enddate]' 
    ORDER BY H.empid DESC"
    $result = mysql_query( $sql );
    $lastid = -1;
    $sum = 0;
    while ( $row = mysql_fetch_assoc( $result )
        $empid = $row["empid"];
        $fname = $row["firstname"];
        $lname = $row["lastname"];
        $eclass = $row["$class"];
        $clockout = $row["clockout"];
        $clockin = $row["clockin"];
        $tdiff = ...PHP code to get time diff from checkin to checkout...
        if ( $empid != $lastid )
            if ( $lastid != -1 )
                echo "<tr><td align=\"right\" colspan="\7\">$sum</td></tr>
                      <tr><td colspan=7><hr></td></tr\n";
            $sum = tdiff;
            echo "<tr><td>$empid</td><td>$fname></td><td>$lname></td>";
        } else {
            $sum += $tdiff;
            echo "<tr><td colspan=3>&nbsp;</td>";
        echo "<td>$eclass</td><td>$clockout</td><td>$clockin</td><td>$tdiff</td></tr>\n";
    echo "<tr><td align=\"right\" colspan="\7\">$sum</td></tr>
          <tr><td colspan=7><hr></td></tr\n";
    TOTALLY untested. Play with it.
    Be yourself. No one else is as qualified.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts