php code to select sum and count of column

Question

there are multiple column in my table ..

     col1   col2  col3     price
     500    700    100       10
     501    700    100       20
     502    700    100       30
     503    700    100       10
      4                      70

I need to get count of col1 and display its sum.

and also display sum of price column...

But the main thing is i need to display this in last row....after all data...how can select this and echo in last row...

plz help me...

I need to echo exactly as i put the data in above table...

I need sql query and also need help to echo only sum of this two column in last row only......

SELECT *,IFNULL(col1,'SUM'), count(*) as count FROM coupon_entry  WHERE Group By col1 WITH ROLLUP


<?php  if (is_array($data)) { foreach($data as $row){ ?>
        <tr>            

            <td><?php echo htmlspecialchars($row['col1']); ?></td>
            <td><?php echo htmlspecialchars($row['col2']); ?></td>          
            <td><?php echo htmlspecialchars($row['col3']); ?></td>
            <td><?php echo htmlspecialchars($row['price']); ?></td>
        </tr>
        <?php } }?>

djoosi · Accepted Answer · 2015-03-13 16:52:22Z

1

One solution is to calculate the sum in PHP with variables :

<?php  if (is_array($data)) {
$totalCol1 = 0; 
$totalPrice = 0;
foreach($data as $row){ 
$totalCol1 += $row['col1'];
$totalPrice += $row['price'];
?>
        <tr>            

            <td><?php echo htmlspecialchars($row['col1']); ?></td>
            <td><?php echo htmlspecialchars($row['col2']); ?></td>          
            <td><?php echo htmlspecialchars($row['col3']); ?></td>
            <td><?php echo htmlspecialchars($row['price']); ?></td>
        </tr>
        <?php } 
        <tr>
            <td><?php echo $totalCol1;?></td>
            <td></td>          
            <td></td>
            <td><?php echo $totalPrice;?></td>
        </tr>
}?>

answered Mar 13, 2015 at 16:52

djoosi

2362 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Soussan Over a year ago

What a waste of time. Just write the query correctly and there is no need to start runnig calculations in PHP.

djoosi Over a year ago

I said that's a solution.. There are lots of solutions !

sunil satpute Over a year ago

<?php echo $totalCol1;?> BUT this display sum Of col1 ..i need to echo count here..how we echo total count....

sunil satpute Over a year ago

FOE Ex- col1 has 4 values like 10,20,30,40 then it display only 4 not sum of all values

Peter Bowers · Accepted Answer · 2015-03-13 16:54:42Z

Either you have to do 2 separate queries or else you have to do your calculations in PHP - either one is fairly simple although the PHP solution will probably be slightly (negligibly?) faster.

double-query:

$r = query('SELECT *
            FROM yada-yada');
while ($row = fetch($r)) {
    echo "<td>$row[col1]</td>\n";
    echo "<td>$row[col2]</td>\n";
    echo "<td>$row[col3]</td>\n";
    echo "<td>$row[price]</td>\n";
}

$r2 = query('SELECT COUNT(*) as cnt, sum(col1) as sum_col1, 
               sum(price) as sum_price
            FROM yada-yada...');
if ($row = fetch($r2)) {
    echo "<td>$row['cnt']</td><td>$row['sum_col1']</td>...$row['sum_price']...\n";
}

calculate in PHP:

$cnt = $sum_col1 = $sum_price = 0;
$r = query('SELECT *
            FROM yada-yada');
while ($row = fetch($r)) {
    echo "<td>$row[col1]</td>\n";
    echo "<td>$row[col2]</td>\n";
    echo "<td>$row[col3]</td>\n";
    echo "<td>$row[price]</td>\n";
    $cnt++;
    $sum_col1 += $row['col1'];
    $sum_price += $row['price'];
}
echo "<td>$cnt</td><td>$sum_col1</td>...$sum_price...\n";

sunil satpute · Accepted Answer · 2015-03-16 12:42:48Z

0

Below is my sql query to get count and sum of column.

SELECT COUNT(coupon) As Total,SUM(Price) AS TotalPrice FROM coupon_entry

And put this in seprate row below table...

<?php   
foreach($tot as $tota) 
?>
        <tr style="background-color:#33CCFF;">
            <td colspan="2" style="text-align:right; font-weight:bold;">Total Coupon</td>

            <td><?php echo $tota['Total'];?></td>
            <td style="text-align:right; font-weight:bold;">Total Amount in Rs</td> 
            <td><?php echo $tota['TotalPrice'];?></td>
        </tr>           
            <?php }?>

answered Mar 16, 2015 at 12:42

sunil satpute

1111 gold badge3 silver badges14 bronze badges

Collectives™ on Stack Overflow

php code to select sum and count of column

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related