num_rows not working when used SUM()

[boney]

Hi,

I want to output a simple result. I join 2 tables and SUM one column in one, i get the SUM results correct but cannot get the num_rows proper.

My Code:

$clientid = 24;

$query = "SELECT items.itemid, SUM(prices.priceid) as PR FROM items 
JOIN prices ON items.itemid = prices.itemid 
WHERE items.clientid = '$clientid'";


$result =  $db_connect -> query($query);
$row_cnt = $result -> num_rows; 

echo $joinrow_cnt."<br/><br/><br/>";

while($assocresult = mysqli_fetch_assoc($result)){

    $price = $assocresult['price'];
    $sumprice = $assocresult['PR'];
    $item = $assocresult['itemid'];
    $realprice = ($price/100)*80;        //after 80% discount

    echo "ItemId: ".$item." Price : " .$price." real price: " .$realprice."<br/>";
    echo "Sum of all items: " .$sumprice;

}

I only get one line of result and the $row_cnt value is only 1.

I want to be able to display the number of rows where items.clientid = '$clientid' and want to SUM(prices.priceid).

P.S. The column 'priceid' contains the actual price.

Thanks in advance !!

[cereal]

SUM() is a function of the GROUP BY clause, so, in order to get correct results you have to add a group by statement to your query, otherwise:

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows. For more information, see Section 12.19.3, “MySQL Handling of GROUP BY”.

More info:

• http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html