Counting (total) of column in DB

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Czar
Forum Commoner
Posts: 58
Joined: Sun Dec 29, 2002 11:17 am

Counting (total) of column in DB

Post 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.
User avatar
discobean
Forum Commoner
Posts: 49
Joined: Sun May 18, 2003 9:06 pm
Location: Sydney, Australia
Contact:

Post by discobean »

SELECT SUM(field_name) AS sum_field_name
FROM table_name

I know this works in SQL Server :P
Czar
Forum Commoner
Posts: 58
Joined: Sun Dec 29, 2002 11:17 am

Post 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;
?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply