I'm trying to sort products on my website by their sales volume. My thought is to create a view that tabulates the quantity sold by product_id over the past 180 days.
I've got a question regarding the query:
SELECT p.products_id, COUNT( op.products_id ) AS num_ordered
FROM orders_products op
JOIN products p
JOIN orders AS o
WHERE op.products_id = p.products_id
AND o.orders_id = op.orders_id
TO_DAYS( NOW( ) ) - TO_DAYS( o.date_purchased )
GROUP BY p.products_id
ORDER BY num_ordered DESC
This works great, but it doesn't include products that have not been purchased during that window. How do I specify that the result should include all products, even ones with zero sales?
Assuming we figure that out, is a view and efficient way to tabulate these results? Would the view automatically be updated whenever a new product is added or a product is ordered? I'm a noobie when it comes to views...