sum group by

ramiwahdan · March 15, 2020

Hi,

I have database, screenshot attached. I have to print all records of attendance sort by ID and have sum group by ID. How to know or figure out when the ID changes so that i can show the total for that ID. Bare with me please and try to show me where is my mistake!

Code:

<?Php

	include('session.php');
	include('dbcon.php');
	
?>

<html>
<head>
<link rel="stylesheet" type="text/css" href="bootstrap.css">
</head>

<body>
	
	<div class="container">
		<div class="row">
			<div class="col m-auto">
				<div class="card mt-5">
					<table class="table table-bordered">
					
						<tr>

							<input type="button" onClick="window.print()" value="Print The Report"/>
							<td>OracleID</td>
							<td>Name</td>
							<td>Designation</td>
							<td>Clocking In Time</td>
							<td>Clocking Out Time</td>
							<td>Duration</td>
						
						</tr>
						
						<?php
						
							$isdone = -1;
							
							$query = "select * from attendance_records where isdone= '".$isdone."' order by OracleID";
							$result = mysqli_query($con, $query);
																			
							$query2 = "SELECT sec_to_time(SUM(timestampdiff(SECOND, ClockingInDate, ClockingOutDate))) as total from attendance_records group by OracleID";
							$result2 = mysqli_query($con, $query2);
							
							while ($row = mysqli_fetch_assoc($result))
							{
								
								$userid = $row['OracleID'];
								$name = $row['Name'];
								$des = $row['Des'];
								$clockingindate = $row['ClockingInDate'];
								$clockingoutdate = $row['ClockingOutDate'];
								$duration = $row['Duration'];
								$t=time();
								$curdate = date("d-m-Y",$t);
								$curTime = date("g:i:s A",$t);
																		
						?>
						
						<tr>
						
							<td><?php echo $userid ?></td>
							<td><?php echo $name ?></td>
							<td><?php echo $des ?></td>
							<td><?php echo $clockingindate ?></td>
							<td><?php echo $clockingoutdate ?></td>
							<td><?php echo $duration ?></td>
		
						</tr>
						
						<?php									
							
							}	
								while($row2 = mysqli_fetch_assoc($result2))
									{
										$totals= $row2['total'];
									
										echo '<td><h5>Total Attendance Time:</h5></td>';
										echo '<td>';
										echo $totals; 
										echo '</td>';
									}						
										
							
								echo '<tr>';
								echo 'Report created by ', $_SESSION['user_id2'], ' on ', $curdate, ' at ', $curTime;
								
								echo '</tr>';
							
						?>
						
					</table>
					
					<a href="home.php" width="100%">Click here to go back to Main Menu</a>
			
				</div>
			</div>
		</div>
	</div>

</body>

</html>

and screenshot of result is attached. I have the totals but not at the end of each OracleID, it is at the end of the report.

Thanks.

mac_gyver · March 15, 2020

what do you want the output to actually be? where do you want to show the totals and do you really want to repeat all the id/name/designation information?

ramiwahdan · March 15, 2020

25 minutes ago, mac_gyver said:

what do you want the output to actually be? where do you want to show the totals and do you really want to repeat all the id/name/designation information?

I am new so it into show ID, name and designation once then attendance records for that ID. the total after each ID group. In my example ID 533349 then total for that then ID 533355 then total for that.

Edited March 15, 2020 by ramiwahdan
Error typo

Barand · March 16, 2020

try

<?php
require 'db_inc.php';
$db = pdoConnect('test');

$res = $db->query("SELECT oracleid
                         , name 
                         , des 
                         , clockingindate   as clockin
                         , clockingoutdate  as clockout
                         , timediff(clockingoutdate, clockingindate) as duration
                         , total
                    FROM attendance_record 
                         JOIN (
                                SELECT oracleid
                                     , sec_to_time(sum(timestampdiff(SECOND, clockingindate, clockingoutdate))) as total
                                FROM attendance_record
                                GROUP BY oracleid
                              ) tots USING (oracleid)
                    ORDER BY oracleid, clockingindate
                    ");

?>
<html>
<body>
        <table border='1' style='border-collapse:collapse; width: 700px;'>
            <tr><th>OracleID</th><th>Name</th><th>Designation</th><th>Clock In</th><th>Clock Out</th><th>Duration</th></tr>
        <?php
            $previd = 0;
            foreach ($res as $row) {
                if ($row['oracleid'] != $previd) {                              // id changed so output total
                    if ($previd != 0) {
                        echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>";
                    }
                    $previd = $row['oracleid'];
                }
                echo "<tr><td>{$row['oracleid']}</td>
                          <td>{$row['name']}</td>
                          <td>{$row['des']}</td>
                          <td>{$row['clockin']}</td>
                          <td>{$row['clockout']}</td>
                          <td>{$row['duration']}</td>
                          </tr>
                     ";
                $total = $row['total'];
            }
            echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>";    // dont forget total for last id
        ?>
        
        </table>
</body>
</html>

image.png.d875a366b3de4c2e703ef4ef97d75602.png

ramiwahdan · March 16, 2020

6 hours ago, Barand said:

try


<?php
require 'db_inc.php';
$db = pdoConnect('test');

$res = $db->query("SELECT oracleid
                         , name 
                         , des 
                         , clockingindate   as clockin
                         , clockingoutdate  as clockout
                         , timediff(clockingoutdate, clockingindate) as duration
                         , total
                    FROM attendance_record 
                         JOIN (
                                SELECT oracleid
                                     , sec_to_time(sum(timestampdiff(SECOND, clockingindate, clockingoutdate))) as total
                                FROM attendance_record
                                GROUP BY oracleid
                              ) tots USING (oracleid)
                    ORDER BY oracleid, clockingindate
                    ");

?>
<html>
<body>
        <table border='1' style='border-collapse:collapse; width: 700px;'>
            <tr><th>OracleID</th><th>Name</th><th>Designation</th><th>Clock In</th><th>Clock Out</th><th>Duration</th></tr>
        <?php
            $previd = 0;
            foreach ($res as $row) {
                if ($row['oracleid'] != $previd) {                              // id changed so output total
                    if ($previd != 0) {
                        echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>";
                    }
                    $previd = $row['oracleid'];
                }
                echo "<tr><td>{$row['oracleid']}</td>
                          <td>{$row['name']}</td>
                          <td>{$row['des']}</td>
                          <td>{$row['clockin']}</td>
                          <td>{$row['clockout']}</td>
                          <td>{$row['duration']}</td>
                          </tr>
                     ";
                $total = $row['total'];
            }
            echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>";    // dont forget total for last id
        ?>
        
        </table>
</body>
</html>

Thanks for the help but i am new to php and don't know how to run the pdo, can you guide me how to connect with mysqli

Barand · March 16, 2020

As it's a query() and not a prepare() the code is the same. Just create a mysqli connection instead.

Sign In

sum group by

Recommended Posts

ramiwahdan

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

ramiwahdan

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

ramiwahdan

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information