[SOLVED] Sum of a mysql column

[zang8027]

I have a table with a column that pulls a decimal value from a mySQL table. Is there a way that I can sum up that entire column?

 

<?php
<table id='menuDisplayTable'>
        <colgroup>
            <col id='restCol' />
            <col id='cityCol' />
            <col id='priceCol'  />
            <col id='timeCol' />
            <col id='delete' />
        </colgroup>
        <thead>
       	<tr>
        <th scope='col'>Restaurant</th>
        <th scope='col'>City</th>
        <th scope='col'>Price</th>
        <th scope='col'>Date</th>
        <th scope='col'>Delete</th>
        </tr>
        </thead>
        <tbody>
";


//we need to color every odd row so that its easier to read. Create a row variable
$rows = 1;
print "<tr>";


while($row = mysql_fetch_array($result))
{
	$ID = $row['recID'];
	$rest = $row['restID'];
	$city = $row['cityID'];
	$price = $row['price'];
	$timedate = $row['time'];


//rows	
print "<td>$restName</td><td>$cityName</td><td>$$price</td><td>$timedate</td><td><a href='processDeleteReceipt.php?deleteID=$ID'>Delete</a></td>";
//If the row ends, and its odd, set the tr class to odd
if($rows==1)
	{	
	print "<tr class='odd'>";
	$rows = 0;

	}else{
	$rows++;
	print "</tr>";
	} //$row else end

}

print "<tr><td>Sum: </tr>";
print"</tbody> </table>";

mysql_close($link);

}
?>

[corbin]

SELECT SUM(column_name) FROM table;

 

 

Or if you wanted to do it in PHP, psuedo code:

 

 

<?php

$total = 0;
while($r = mysql_fetch_assoc($q)) {
    $total += $r['column_that_holds_value_you_want_to_add'];
}

 

I suggest doing it in MySQL though, especially if you don't plan to pull all of the values to begin with.  If you do it in MySQL, it will most likely do a full table scan, so if it's a large table, you might run into issues with it, but in the same sense, if it's a large set of data, PHP would take a while too.

[zang8027]

ok, what do i set the variable to?

 

 

$query="SELECT SUM(price) FROM clientReceipt WHERE userID = $userID";
$result=mysql_query($query);

print "<tr>";
while($row = mysql_fetch_array($result))
{
	//Here is where im confused
               $sum = $row['price'];
	print "<td>Sum: $sum </td>";
}


print"</tr></tbody> </table>";

[corbin]

You either need to use a numeric index (mysql_fetch_row or mysql_Fetch_array with numeric indexes enabled), or you'll need to use an alias:

 

 

SELECT SUM(price) AS price_total FROM clientReceipt WHERE userID = $userID

 

 

Then you would use price_total as the name.

[zang8027]

thank you! I learn so much here ha

[corbin]

No problem ;p.