Multiplication, Division, and Addition In MYSQL

Question

I am running an A-B test to see what style of page might get more orders of products and trying to weigh one page as "better" than others because it shows more frequently.

I have 8 columns in my MYSQL database.

1. Orders_Page_One
2. Orders_Page_Two
3. Orders_Page_Three
4. Visitors_Page_One
5. Visitors_Page_Two
6. Visitors_Page_Three
7. Total_Orders
8. Total_Visitors

Currently I am only running a query as:

$result = mysql_query("SELECT *, (Total_Orders / Total_Visitors) AS order_percent FROM orders WHERE category = clothes ORDER BY order_percent DESC LIMIT 5") or die(mysql_error());

This works BUT

This allows me to show which orders are most popular, but not take into account which page they appear on.

I have tried:

$result = mysql_query("SELECT *, (Orders_Page_One / Visitors_Page_One) + (Orders_Page_Two / Visitors_Page_Two) + (Orders_Page_Three / Visitors_Page_Three) AS order_percent FROM orders WHERE category = clothes ORDER BY order_percent DESC LIMIT 5") or die(mysql_error());

But this doesn't seem to give accurate results.

I also tried

$result = mysql_query("SELECT *, ((Orders_Page_One / Visitors_Page_One) + (Orders_Page_Two / Visitors_Page_Two) + (Orders_Page_Three / Visitors_Page_Three)) AS order_percent FROM orders WHERE category = clothes ORDER BY order_percent DESC LIMIT 5") or die(mysql_error());

But this gives a syntax error.

Assuming:

Orders Page One = 10 and Visitors Page One = 20 
Orders Page Two = 10 and Visitors Page Two = 40 
Orders Page Three = 10 and Visitors Page Three = 50 

The mathematical result I want would be:

(10 / 20) + (10 / 40) + (10 / 50)

OR

0.5 + 0.25 + 0.20 = 0.95

Any suggestions? I think I am just not setting the math up correctly.

After more reading I think I'll need to use Multiple selects:

IE:

    Select (orders_page_1/visitors_page_1) as order_percent_1 from orders
    Select (orders_page_2/visitors_page_2) as order_percent_2 from orders
    Select (orders_page_3/visitors_page_3) as order_percent_3 from orders
    Select (order_percent_1 + order_percent_two + order_percent_3) as order_percent
    WHERE category = clothes
    ORDER BY order_percent DESC

But not sure on formatting? Or if this would work?

$result = mysql_query("

SELECT * FROM orders, (
    SELECT (orders_page_1 / visitors_page_one) AS ord1 FROM orders WHERE category = 'clothing' ORDER BY ord1 DESC LIMIT 2) AS ord_1,
      (
    SELECT (orders_page_2 / visitors_page_two) AS ord2 FROM orders WHERE category = 'clothing' ORDER BY ord2 DESC LIMIT 2) AS ord_2,
      (
    SELECT (orders_page_3 / visitors_page_3) AS ord3 FROM orders WHERE category = 'clothing' ORDER BY ord3 DESC LIMIT 2) AS ord_3,

    ORDER BY (ord_1 +ord_2 + ord_3) DESC LIMIT 2") or die(mysql_error());

However this is giving the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY (ord_1 +ord_2 + ord_3) DESC LIMIT 2' at line 8

Answer 1

One possible problem is that

WHERE category = clothes

should really be

WHERE category = 'clothes'

try it now and see what you get.