CODEWITHSUNDEEP
phpmysqlsql
Vishal Kumar
Vishal Kumar

Reputation: 4617

Sql query to display all products in order of their daily sales

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...

PRODUCTS

PRODUCT 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.

SCREEN

I am a beginner with SQL...and not sure What to write. This is what I tried but not working.

SELECT DISTINCT p1.name,
                p1.id
FROM products p1
LEFT JOIN
product_sales p2
    ON p1.id = p2.product_id
ORDER BY SUM(p2.quantity)

Upvotes: 0

Views: 4247

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

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

Upvotes: 1

Related Questions