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

CodePudding user response：

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.

CodePudding user response：

Try this with eloquent:

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

CodePudding user response：

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');