Mysql question: trying to combine sales total from 2 statuses

[Guest]

Hola I've managed to get the sales total for each status to display in my order manager (heavily modified osc_active_desktop...)

 

With the following code:

 

$command8_query = "select SUM(value) as total_value FROM orders_total, orders WHERE orders.orders_id=orders_total.orders_id AND orders.orders_status='4' AND orders_total.class='ot_total'";

$result2 = tep_db_query($command8_query);
$row2 = tep_db_fetch_array($result2);
$Total2 = $row2["total_value"];

 

But I'd like to include 2 statuses together - the pending as well as the paid - so that we can see exactly how much money we have in unshipped orders at any given time.

 

I tried using this:

 

$command8_query = "select SUM(value) as total_value FROM orders_total, orders WHERE orders.orders_id=orders_total.orders_id AND orders.orders_status='4' OR orders.orders_status='1'  AND orders_total.class='ot_total'";

 

Which creates a bunch of duplicates or something - as the total is way out of the ballpark, and not an accurate sum of the 2 statuses total amounts.

 

Any ideas?

[Guest]

$command8_query = "SELECT SUM(ot.value) as total_value 
			   FROM orders o, orders_total ot 
			   WHERE o.order_status IN (1,4) 
			   	AND o.orders_id=ot.orders_id 
				AND orders_total.class='ot_total' 
			   GROUP BY o.orders_id ";

If you use an aggregate function you will need to group by an atomic column. Try the SQL above...

[Guest]

Thanks very much! - this worked great, I had to change it a little - what finally worked for me was

 

$command10_query = "SELECT SUM(orders_total.value) as total_value FROM orders, orders_total WHERE orders.orders_status IN (1,4) AND orders.orders_id=orders_total.orders_id AND orders_total.class='ot_total'  ";

$result3 = tep_db_query($command10_query);
$row3 = tep_db_fetch_array($result3);
$Total3 = $row3["total_value"];