Search code examples
phplaraveleloquentmodelwhere-clause

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

Solution

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