0

I've got a table of 'userorders' which have orderID, userID , orderStatus (which can be 1,2,3) and orderTime.

I want to calculate percent of last 150 orders, in last 6 month for userid = 1 which has orderStatus 1.

I tried to write two queries for both orders Status (1, 2/3) and then calculate percent of orders but my queries are not correct.

my code and queries:

$rs1 = mysql_query("select count(*) as orderCount1 from userorders where 
         orderStatus = 1 and orderID in (select top 150 orderID from userorders where 
          userid = 1 and orderStatus in (1,2,3)  and  
         orderTime > ".strtotime("-6 month")." oder by orderID desc)") or  
         die (mysql_error());

$rs2 = mysql_query("select count(*) as orderCount1 from userorders where 
         orderStatus in (2,3) and orderID in (select top 150 orderID from userorders where 
          userid = 1 and orderStatus in (1,2,3)  and  
         orderTime > ".strtotime("-6 month")." order by orderID desc)") or  
         die (mysql_error());


$orderCount1 = $rs1['orderCount1'];
$orderCount2 = $rs2['orderCount2'];

$orderPercent = ($orderCount1/ $orderCount1+$orderCount2)*100;

How can I solve the problem or improve my codes.

Ali
  • 243
  • 1
  • 5
  • 16
  • You want to find percent of orderStatus=1 in all orders? – vinculis Nov 03 '12 at 11:43
  • http://stackoverflow.com/questions/1576370/getting-a-percentage-from-mysql-with-a-group-by-condition-and-precision – ethrbunny Nov 03 '12 at 12:30
  • @ethrbunny I check this Q and A, for the answer it returns 50 numbers which are avrage of agents but I want to get one number which is average of ordestatus. – Ali Nov 03 '12 at 20:27
  • @vinculis actually orderStatus can be 1,2,3,... but in this case I want to find percent of orderStatus when it is 1 and 2,3. – Ali Nov 03 '12 at 20:33

1 Answers1

0

I find out the right query.

It has one main query which has most conditions and the percent of order is calculated from output of main query :

$rs = mysql_query("select (sum(case when orderStatus = 1 then 1 else 0 end) 
         /count(orderStatus))*100 as percentage from (select orderStatus from  
         userorders where orderStatus in (1,2,3) and userid = 1 and  
         orderTime > ".strtotime("-6 month")."  order by orderID desc limit 
          150) tempTable") or die (mysql_error());

$percentage1 = $rs['percentage'];

for orderStaus 2,3

$percentage2 = 100 - $percentage1;
Ali
  • 243
  • 1
  • 5
  • 16