MySQL: ORDER BY stat / age?

Question

I have an int field in my product table called product_stat which is incremented everytime a product is looked at. I also have a date field called product_date_added.

In order to figure out the average visits per day a product gets, you need to calculate how many days the product has existed by using the current date and the date the product was added. Then divide the product stat by the amount of days it has existed to get the average visits per day.

OK but what I want to do is select a number of products and order them by visits per day DESC

How can I do that?

Thanks!!!

Answer 1

Something like this should do the trick, using DATEDIFF to get the difference between two dates, and then dividing the product_stat column by that difference.

SELECT 
    p.*, 
    p.product_stat/DATEDIFF(CURDATE(),p.product_date_added) as visits_per_day
FROM products p
ORDER BY visits_per_day DESC

Although note that DATEDIFF only came around as of MySQL 4.1.1. If you're using an earlier version you should do "TO_DAYS(CURDATE()) - TO_DAYS(p.product_date_added)" instead.

Answer 2

You will want something like this:

SELECT product_name, 
product_stat / datediff(now(), product_date_added) as 'VisitPerDay'
FROM product
ORDER by VisitPerDay DESC