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

Count sum of column where month from d-m-Y date format laravel

I have two columns called price and date, so i want to sum the total of price where the month is 01:

 price |   date
-------------------
200    | 01-01-2022
100    | 09-01-2022
400    | 01-03-2022
120    | 01-06-2022

Here my code:

$total = Products::whereMonth('date', '=', 1)->sum('price');

But its not showing any data its only showing 0

Tags:

2

Answers


  1. 0

    Because you cannot change the format of things, we have to selectRaw from the database:

    • Step 1) Is to parse the date using your format "%d-%m-%Y" you may read more on str_to_date function from: https://www.w3schools.com/sql/func_mysql_str_to_date.asp

    • Step 2) we get the month from the parsed date

    • Step 3) we assign the parsed date as "modified_date" or whatever you
      like to rename it as.

    • Step 4) we select and sum based on the result.

    This should be the final result:

    $products = Products::selectRaw('*, month(str_to_date(date, "%d-%m-%Y")) as modified_date')->get();

$total = $products->where('modified_date', 1)->sum('price'));

    Result:

    300

    Now you can search by months 1 -> 12 while maintaining the model & all relationship loading date for other usage.

    Login or Signup to reply.
  2. 0

    Using DB raw query ,First convert string date to MySQL date format using STR_TO_DATE method and then use MONTH function to add condition to select month based result.

     Products::where(DB::raw("(MONTH(STR_TO_DATE(date,'%d-%m-%y')))"),1)->sum('price')

    Also for reusable purpose you can use laravel scope

    in Product model add below method

    public function scopeByMonth($query,$month)
{
   $query->where(DB::raw("(MONTH(STR_TO_DATE(date,'%d-%m-%y')))"),$month);
}

    and query will be

    Products::byMonth(1)->sum('price');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search