CODEWITHSUNDEEP
phpmysql
sunil satpute
sunil satpute

Reputation: 111

php code to select sum and count of column

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 } }?>

Upvotes: 0

Views: 3664

Answers (3)

sunil satpute
sunil satpute

Reputation: 111

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 }?>

Upvotes: 0

Peter Bowers
Peter Bowers

Reputation: 3093

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";

Upvotes: 0

djoosi
djoosi

Reputation: 236

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>
}?>

Upvotes: 1

Related Questions