CODEWITHSUNDEEP
phpmysql
payal dubey
payal dubey

Reputation: 69

php code to display sum of column from total

My query gets below data from database.

This is correct but i also need to display sum of all rows like below total..

how can i do this...

I need to display sum of totalbooking column in last row.. 

bookno  bookingdate  totalbooking
 401      15-03-2015     1
 401      16-03-2015     2
 404      15-03-2015     1

          Total          4

Below is my code...

<?php
if($_POST && isset($_POST['Submit']))
{   
    if($_POST['hawkername']=='All')
    {   
    $cityname = $_SESSION['Auth']['city'];
    $data = $database->getRows("SELECT RE.book_no AS book, RE.bookingdate AS bookingdate, HM.hawker_name AS hawkername,SAM.tehsil AS centername,
    COUNT( DISTINCT RE.receipt_no ) AS totalbooking,SUM(DISTINCT RE.receipt_no) AS final FROM receipt_entry RE INNER JOIN hawker_master HM ON HM.id = RE.hawkername INNER JOIN sub_agent_master SAM ON SAM.id = RE.subagentname WHERE RE.city_name = :cityname GROUP BY RE.hawkername, RE.book_no",array(':cityname'=>$cityname)); 
    } 

?>


<?php  if (is_array($data)) {    foreach($data as $row){  ?>
        <tr>    
        <td><?php echo htmlspecialchars($row['book']); ?></td>
        <td><?php echo htmlspecialchars($row['bookingdate']); ?></td>           
        <td><?php echo htmlspecialchars($row['totalbooking']); ?></td>
        </tr>
        <?php  } }  ?>  
        <tr>        
        <td>Total</td>
        <td></td>
        </tr>

Upvotes: 0

Views: 1158

Answers (2)

christophetd
christophetd

Reputation: 3874

Change your query into this:

SELECT 
    (SELECT SUM(DISTINCT RE.receipt_no) FROM FROM receipt_entry RE) as totalbooking, 
    RE.book_no AS book, RE.bookingdate AS bookingdate, 
    ...

Upvotes: 1

payal dubey
payal dubey

Reputation: 69

<?php  if (is_array($data)) {  $total = 0; foreach($data as $row){ $total += $row['totalbooking'];  ?>
        <tr>        

        <td><?php echo htmlspecialchars($row['book']); ?></td>
        <td><?php echo htmlspecialchars($row['bookingdate']); ?></td>           
        <td><?php echo htmlspecialchars($row['totalbooking']); ?></td>
        </tr>

        <?php $i++; } }  ?> 
        <tr>
        <td colspan="4"></td>
        <td style="font-size:13px; font-weight:bold;">Total</td>
        <td style="font-size:13px; font-weight:bold;"><?php echo $total; ?></td>
        </tr>

Upvotes: 0

Related Questions