SUM in a nested query

deos not total value but only count rows?

[henry0]

I am trying to get the total of order passed by a given customer (this is a test so customer id is hard coded)

My problem is that SUM seems to count the rows but does not addition all the value contained within each row
The first query returns the expected data.
In my example I know that I have 4 rows that are qualified by the query and the total number of order should be 5 but it returns 4?
What may I do
Use anther method?

$conn=db_connect ();
$sql = "
SELECT cart_order_id
FROM Cart_order_sum
WHERE customer_id='1' "; //echo"SQL $sql<p>";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result) )
{
$cart_order_id= $row['cart_order_id']; //echo"cart order id: $cart_order_id<p>";

$sql2 = "
SELECT SUM(quantity)
FROM Cart_order_inv
WHERE
cart_order_id='$cart_order_id' AND productCode='one-1' ¦¦ productCode='next_y' ¦¦ productCode='two-2' ";

$result2 = mysql_query($sql2);
}
$ttl=(mysql_result($result2, "quantity"));
echo"TTL: $ttl";

[Habtom]

while($row = mysql_fetch_array($result) )
{
$cart_order_id= $row['cart_order_id']; //echo"cart order id: $cart_order_id<p>";

$sql2 = "
SELECT SUM(quantity)
FROM Cart_order_inv
WHERE
cart_order_id='$cart_order_id' AND productCode='one-1' ¦¦ productCode='next_y' ¦¦ productCode='two-2' ";

$result2 = mysql_query($sql2);
}

while($row = mysql_fetch_array($result) )
{
$cart_order_id= $row['cart_order_id']; //echo"cart order id: $cart_order_id<p>";

$sql2 = "
SELECT SUM(quantity) as sum_quantity
FROM Cart_order_inv
WHERE
cart_order_id='$cart_order_id' AND productCode='one-1' ¦¦ productCode='next_y' ¦¦ productCode='two-2' ";

$result2 = mysql_query($sql2);
$sumq = $sumq + $result2['sum_quantity'];
}

[henry0]

Hatbon thanks
Here is what I have

$sql2 = "
SELECT SUM(quantity)as sum_quantity
FROM CubeCart_order_inv
WHERE
cart_order_id='$cart_order_id' AND productCode='one-1' ¦¦ productCode='next_y' ¦¦ productCode='two-2' ";

$result2 = mysql_query($sql2);

$sumq=$sumq+$result2['sum_quantity'];
}
//$ttl=(mysql_result($result2, "quantity"));
echo"TTL: $sumq";

aside throwing an undefine index: $sumq
I got 0

[Habtom]

echo $sql2

and run the query separately, then you know if you are having the sql statement set properly.

Habtom

[henry0]

It does fine,
it returns the expected 4 values

and results in EXPECTED 5

so the problem lies in echoing the result

[edit]
sorry if I don't answer, I will be away for the next few hours
[/edit]

[henry0]

OK I resolved the problem by doing

$result2 = mysql_query($sql2);
$row=mysql_fetch_assoc($result2);
$ttl=$row['quantity']+1;
}
echo"TTL: $ttl";

reason is the SUM starts at 0
it works but I do not feel quite happy with the solution
sounds kind of a sloppy job!