how to sum value of every last date based on product code in laravel 8

Assumed the table name is products - this is a query which sum the value of the most recent record per group

SELECT sum(p1.value) as total
FROM products p1 LEFT JOIN products p2
 ON (p1.product_code = p2.product_code AND p1.date < p2.date)
WHERE p2.product_code IS NULL;

In Query Builder

$total = DB::table('products as p1')
    ->selectRaw('SUM(p1.value) as total')
    ->leftJoin('products as p2', function ($join) {
        $join->on('p1.product_code', '=', 'p2.product_code')
            ->where('p1.date', '<', 'p2.date');
    })
    ->whereNull('p2.product_code')
    ->value('total');

If your database version supports window functions, then:

WITH ranked AS (
  SELECT products.*, 
         RANK() OVER (PARTITION BY product_code ORDER BY date DESC) AS rn
  FROM products
)
SELECT sum(value) as total
FROM products 
WHERE rn = 1;

thanks @tykus , sorry .... but this is my question for result Result : // product_code date value // a1 2023-01-21 20 //--> last date a1 = 2023-01-21 with value 20 // b1 2023-01-20 20 //--> last date b1 = 2023-01-20 with value 20 // c1 2023-01-20 20 //--> last date c1 = 2023-01-20 with value 20 //

sum value from last date each product code --> 20 + 20 + 20 = 60

The table name is: products

Query Builder query:

DB::table('products as p1') ->select('p1.*') ->leftJoin('products as p2', function ($query) { $query->where('p2.product_code', '=', DB::raw( 'p2.product_code')) ->where('p2.date', '>', DB::raw('p1.date')); }) ->whereNull('p2.date') ->sum('p1.value');

SQL Query:

select SUM(p1.value) as total from products as p1 left join products as p2 on p2.product_code = p1.product_code and p2.date > p1.date where p1.date is null;

thankyou sir @ronakgolakiya, but i want the result is evey product value only last date (latest) a1 2023-01-21 20 ---> last date a1 = 2023-01-21 with value 20 (latest date of a1) b1 2023-01-20 20 ---> last date b1 = 2023-01-20 with value 20 (latest date of b1) c1 2023-01-20 20 ---> last date c1 = 2023-01-20 with value 20 (latest date of c1) then sum value of latest date each product = 20+20+20 = 60 thankyou

Okay, Got it your point. I found one mistake in my code. Try again with the below code.

DB::table('products as p1') ->select('p1.*') ->leftJoin('products as p2', function ($query) { $query->where('p2.product_code', '=', DB::raw('p1.product_code')) ->where('p2.date', '>', DB::raw('p1.date')); }) ->whereNull('p2.date') ->sum('p1.value');

awesome, thank you very much sir @ronakgolakiya ... its work,

one more sir @ronakgolakiya, if i'm only want sum in january 2023, how edit the code ? thank you

You need to add this condition: ->whereBetween('p1.date', ['2023-01-01', '2023-01-31']) You can change it according to your timeframe.

DB::table('products as p1') ->select('p1.*') ->leftJoin('products as p2', function ($query) { $query->where('p2.product_code', '=', DB::raw('p1.product_code')) ->where('p2.date', '>', DB::raw('p1.date')); }) ->whereNull('p2.date') ->whereBetween('p1.date', ['2023-01-01', '2023-01-31'])->sum('p1.value');

sorry sir @ronakgolakiya , i try this code : DB::table('products as p1') ->select('p1.*') ->leftJoin('products as p2', function ($query) { $query->where('p2.product_code', '=', DB::raw('p1.product_code')) ->where('p2.date', '>', DB::raw('p1.date')); }) ->whereNull('p2.date') ->whereBetween('p1.date', ['2023-01-01', '2023-01-31']) ->sum('p1.value'); no result.

i also try modified the code : DB::table('products as p1') ->select('p1.*') ->leftJoin('products as p2', function ($query) { $query->where('p2.product_code', '=', DB::raw('p1.product_code')) ->where('p2.date', '>', DB::raw('p1.date')); }) ->whereNull('p2.date') ->whereYear('p1.date', '=','2023') ->whereMonth('p1.date', '=','1') ->sum('p1.value')

also no result

@arief19 Do you mean, you don't want to sum of latest date product_code's value sum? But, Get the sum of all product_code's values which have a date between In January 2023.

yes sir @ronakgolakiya

i want to sum value all product_code's of latest date product in january 2023