Question 16082 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to get the product name with the highest value by month? – Laravel

I’m trying to display a data by month and displays the product with the highest value or bought quantity. But I don’t know how I should do it.

This is the code I have and tried so far

public function viewSales(){
   
    $data = sales::select(
        [sales::raw("SUM(product_fee) as product_fee, MONTHNAME(created_at) as month_name, MAX(bought) as bought"),'product']
    )->whereYear('created_at', date("Y"))
    ->orderBy('created_at', 'asc')
    ->groupBy('month_name')
    ->get();
     return view('admin.viewSales', compact('data'));
}

This is what’s inside my database

id product bought product_fee created_at
1 Dictionary 1 200 2023-01-14 18:55:34
2 Horror 3 100 2023-01-15 17:55:34
3 How to cook 5 300 2023-01-16 11:55:34

and this is what I’m trying to display

Most bought product sold overall earning
How to cook 5 600
Tags:

2

Answers


  1. 0

    i think you can try this :

    public function viewSales(){
   
    $data = sales::select(
        [sales::raw("(bought*product_fee) as overall_earning, MONTHNAME(created_at) as month_name, MAX(bought) as bought"),'product']
    )->whereYear('created_at', date("Y"))
    ->orderBy('created_at', 'asc')
    ->groupBy('month_name')
    ->get();
     return view('admin.viewSales', compact('data'));
}

    I hope this works for you

    Login or Signup to reply.
  2. 0

    Below SQL will give you the desired output for current year and month.

    Fetch for current year

    SELECT 
  product as most_bought_product_for_the_year,
  bought as sold, 
  (SELECT SUM(product_fee) from products WHERE YEAR(created_at) = year(curdate())) as overall_earning 
FROM `products` 
WHERE 
  bought = (SELECT MAX(bought) from products WHERE YEAR(created_at) = year(curdate()));

    Fetch for months of the current year

    SELECT
  product as most_bought_for_the_month,
  bought as sold,
  MONTHNAME(created_at) as month_name,
  SUM(product_fee) as overall_earning
FROM `products` 
WHERE 
  YEAR(created_at) = YEAR(curdate())
AND 
  bought IN (SELECT MAX(bought) from products WHERE YEAR(created_at) = YEAR(curdate()) GROUP by MONTH(created_at))
GROUP BY MONTH(created_at)
ORDER BY MONTH(created_at);

    SQL Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search