Solve Query for Showing Top 5 Selling Products

Solve QUERY for showing TOP 5 selling Products

select top 5 Item_code, sum(Quantity)
from customer_invoice
group by Item_code
Order by sum(Quantity) desc

How can I query this sql to get the best-selling product

You can use a SQL join between the tables on ProID, e.g.

from products as p
inner join orderdetails as od
   on p.ProID = od.ProID

You can use group by syntax to ensure you get distinct rows, e.g.

group by p.ProID

Use aggregation function such as sum, count and avg to find out the totals in a select, e.g.

select sum(od.OrderQuantity) as total

Use order by syntax to show the top answers, e.g.

order by sum(od.OrderQuantity) desc

Use limit to show top n results, e.g.

limit 5

Hopefully that gives you enough pointers to formulate the SQL yourself.

notes, I've given the tables an alias to ensure you don't get conflicts between the column names. You can't reference total in your order statement due to the way SQL calculates the dataset. I've used an inner join, but you might want to look into left & right joins.

Finding the 3 top selling products in each category

You can join the tables and aggregate to get the total sales for each product.

Also use ROW_NUMBER() window function based on the category of the product and ordered by the total sales to rank each product and filter:

SELECT id, category, sales
FROM (
  SELECT p.id, 
         MAX(category) category, 
         SUM(o.quantity) sales,
         ROW_NUMBER() OVER (PARTITION BY MAX(category) ORDER BY SUM(o.quantity) DESC) rn
  FROM Products p INNER JOIN OrderItems o
  ON o.prodID = p.id
  GROUP BY p.id
) t
WHERE rn <= 3
ORDER BY id;

Or, aggregate first in OrderItems and then join:

SELECT id, category, sales
FROM (
  SELECT p.id, 
         p.category, 
         o.sales,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY o.sales DESC) rn
  FROM Products p 
  INNER JOIN (
    SELECT prodID, SUM(quantity) sales
    FROM OrderItems
    GROUP BY prodID
  ) o ON o.prodID = p.id
) t
WHERE rn <= 3
ORDER BY id;

See the demo.

Display best selling items by status

if you relationship is done between this table already, you can use this code, if not you have to go to the OrderDetails Model and add new method orders

$top_sell_items = OrderDetails::with(['product', 'orders'])
             ->whereHas('orders', function($query) {
                $query->where('status', 'delivered');
              })
             ->select('product_id', DB::raw('SUM(quantity) as count'))
             ->groupBy('product_id')
             ->orderBy('count', 'desc')
             ->take(3)
             ->get();

---

Related Topics