Page 1 of 1

Counting (total) of column in DB

Posted: Wed Jun 11, 2003 12:27 am
by Czar
An easy one. A query that returns a sum of all values (INT) of a single column (total of a shopping cart) ?. Thx.

Posted: Wed Jun 11, 2003 1:47 am
by discobean
SELECT SUM(field_name) AS sum_field_name
FROM table_name

I know this works in SQL Server :P

Posted: Wed Jun 11, 2003 6:31 am
by Czar
Maaan... I can't figure it out. No matter what are the values in column, the result is always 20 (??) Straighten it out, please.

In simplified form it goes something like this:

Code: Select all

<?php
$totalrow = "SELECT price FROM cart"; # column that holds the prices of items in cart
$total = mysql_query($totalrow) or die(mysql_error());
$sumtotal = mysql_fetch_array($total);
$sum = array_sum($sumtotal);

echo $sum;
?>

Posted: Wed Jun 11, 2003 6:49 am
by twigletmac
The reason why your code isn't working is because you are only ever getting the first value of price - you are not summing a bunch of numbers, only one. You could do:

Code: Select all

<?php 
$totalrow = "SELECT price FROM cart"; # column that holds the prices of items in cart 
$total = mysql_query($totalrow) or die(mysql_error()); 
$sum = 0;
while ($row = mysql_fetch_assoc($total) {
    $sum += $row['price'];
}
echo $sum; 
?>
but it would be much easier to use MySQL to do this - as discobean said:

Code: Select all

<?php
$sql = "SELECT SUM(price) AS price FROM cart";

$result = mysql_query($sql) or die(mysql_error()); 
$row = mysql_fetch_assoc($result);
$sum = $row['price'];
Mac