SQL statement - best sellers for a time period

dinosoup

Hi all. I need to write a function to display the best sellers (most quantity sold) for say the last 3 months.

There's only 2 tables involved.

ORDERS
orders_id
date_purchased

ORDERS_PRODUCTS
orders_products_id
orders_id
products_id
products_quantity

ORDERS and ORDERS_PRODUCTS are the tables and the others are their fields. Some fields were omitted.

This is the SQL statement I came up with. But I'm not sure if it's correct.

SELECT DISTINCT products_id, SUM( products_quantity ) AS num_ordered
FROM orders
INNER JOIN orders_products ON orders.orders_id = orders_products.orders_id
WHERE date_purchased >= '2006-12-21 11:10:06' AND date_purchased <= '2007-12-21 11:10:06'
GROUP BY products_id
ORDER BY num_ordered DESC

Please advise. Thanks.

Azala

You should deal with all date/time stuff within SQL and as such I recommend using INTERVAL within a DATE_SUB.

For example:

WHERE date_purchased >= DATE_SUB(NOW(), INTERVAL 3 MONTH)

Which should give you any order from now to exactly 3 months ago. The great thing here is that MySQL deals with all the date conversion/calculation which ensures compability with the datetime field.

Looking at your tables and then SQL I don't really see why you're using two tables. Is date_purchased the only thing the ORDERS table is for? If so i'd scrap that and combine the two which would remove the JOIN and speed up the SQL query.

dinosoup

Thanks I didn't know about the date within SQL.

I'm using an existing database which is set up like this.

"orders" table:
http://www.filepulse.org/files/100/orders.gif

"orders_products" table:
http://www.filepulse.org/files/100/orders_products.gif