Finding the sum of a sum?

[PapaBurgundy]

Hi,

 

I have a mysql_query within PHP that multiplies the price of a product by the quantity bought of that product to give a subtotal.

 

products.product_price * order_prod.order_quan AS product_subtotal

 

This works fine and is called upon using

 

($get_data['product_subtotal']

 

As you would expect.

 

 

My only problem is that I need to get a total order cost.

 

I thought that it would merely be a case of doing something like

SUM(product_subtotal) AS total_order_price

 

But this brings back nothing.

 

I know this must be very simple as it's only adding together a few values, but I've been stuck on this same problem for hours and time is running out ahhh

 

Any ideas?

[mikosiko]

post more code

[PapaBurgundy]

          $sql = "SELECT         products.product_id, 
				   products.product_price, 
				   order_prod.order_quan, 
				   products.product_price * order_prod.order_quan AS product_subtotal"."
				   FROM products, order_prod"." 
				   WHERE products.product_id = order_prod.product_id "." 
				   AND order_prod.order_id = " . $_GET["order_id"]." 
				   ORDER BY products.product_id";
		$get = mysql_query($sql);
	if(mysql_num_rows($get)==0){
		echo "Sorry but there is nothing available.";
		}else{
			while ($get_data = mysql_fetch_assoc($get)){
				echo $get_data['product_subtotal'];

 

Some more code as requested, I hope this helps!

[fenway]

Aggregating a SUM usually requires a derived table.

[PapaBurgundy]

Aggregating a SUM usually requires a derived table.

 

I sort of realized that through my trial and error haha but was hoping it would work anyway.

 

Is there actually a way to total a set of returned values from a sum?

[fenway]

Show me what sample output you're looking for.

[PapaBurgundy]

$sql = "SELECT         products.product_id, 
				   products.product_price, 
				   order_prod.order_quan, 
				   products.product_price * order_prod.order_quan AS product_subtotal,
                                           SUM(product_subtotal) AS total_order_price"."----------------------------------sum line which doesn't work
				   FROM products, order_prod"." 
				   WHERE products.product_id = order_prod.product_id "." 
				   AND order_prod.order_id = " . $_GET["order_id"]." 
				   ORDER BY products.product_id";
		$get = mysql_query($sql);
	if(mysql_num_rows($get)==0){
		echo "Sorry but there is nothing available.";
		}else{
			while ($get_data = mysql_fetch_assoc($get)){
				echo $get_data['product_subtotal'];
                                        echo $get_data['total_order_price'];-----------------------------total order price echoed here

 

 

I just want to add up the values of the subtotals of each product.

 

So my product_subtotal would be multiplying the quantity of the product by the price of the product.

 

I then want to add all of those quantity subtotals together to give an order total.

 

So sample output,

 

Product name       Quantity Product price Subtotal

product name1 1         £350.00         £350.00

product name2 4         £70.00         £280.00

 

Order total price

£630.00

 

I just want to add together the subtotals to get a total order price.

 

Sorry if my explanation isn't great; my brain has been used and abused this past few weeks.

 

 

[fenway]

Just a second -- I'm confused why you want to do this in the DB?