I have 2 simple tables in my Retail POS app..called products and product_sales. The product_sales records all the sales made in different orders. I want to display a page that lists all my products sorted by the number of their sales...
Now I want to write a SQL query that could fetch all my products ... I think a left join... that could show the sum of all the sales made for that product...and sort them as RANK i.e. top selling Items.
I am a beginner with SQL...and not sure What to write. This is what I tried but not working.
SELECT DISTINCT p1.name,
FROM products p1
ON p1.id = p2.product_id
ORDER BY SUM(p2.quantity)
SELECT t1.name, COALESCE(t2.totalQuantity, 0) FROM products t1 LEFT JOIN ( SELECT product_id, SUM(quantity) AS totalQuantity FROM product_sales GROUP BY product_id ) t2 ON t1.id = t2.product_id ORDER BY t2.totalQuantity DESC -- show highest selling product first