$year = date("y");
for($i=1;$i<=12;$i++)
{
$MonthlyReceive = DB::table('order_items') ->whereBetween('created_at',array($year.'-'.$i.'-1',$year.'-'.$i.'-31')) ->select(DB::raw('sum(price*quantity)'))->where('quantity','<','0');
return $MonthlyReceive;
}
// table name "order_items"
// id |product_id |quantity |price |order_id
-
I want per month total receiving amount, so please give me a solution in this for loop.sr_atiq– sr_atiq2018-11-14 06:32:55 +00:00Commented Nov 14, 2018 at 6:32
-
1What's the error or unexpected behaviour you receive?Shadow– Shadow2018-11-14 06:40:53 +00:00Commented Nov 14, 2018 at 6:40
-
receiving: [[{sum(pricequantity): null}], [{sum(pricequantity): null}], [{sum(pricequantity): null}],…] 0: [{sum(pricequantity): null}] 0: {sum(pricequantity): null} 1: [{sum(pricequantity): null}] 2: [{sum(pricequantity): null}] 3: [{sum(pricequantity): null}] 4: [{sum(pricequantity): null}] 5: [{sum(pricequantity): null}] 6: [{sum(pricequantity): null}] 7: [{sum(pricequantity): null}] 8: [{sum(pricequantity): null}] 9: [{sum(pricequantity): null}] 10: [{sum(pricequantity): -16800}] 11: [{sum(pricequantity): null}]sr_atiq– sr_atiq2018-11-14 07:06:17 +00:00Commented Nov 14, 2018 at 7:06
-
its work on array 11 value is okay but does not show in my dasgboard graphsr_atiq– sr_atiq2018-11-14 07:07:03 +00:00Commented Nov 14, 2018 at 7:07
-
@sr_atiq If possible please add your error with some explanation, It's better for understanding.irfanengineer– irfanengineer2018-11-14 07:18:41 +00:00Commented Nov 14, 2018 at 7:18
|
Show 2 more comments
2 Answers
You can get aggregated data monthly wise without the loop.
$MonthlyReceive = DB::table('order_items')
->select(DB::raw('sum(price*quantity) as amnt'))
->whereRaw('date(created_at) between "'.$year.'-01-01" and "'.$year.'-12-31"')
->where('quantity','<','0')
->groupBy(DB::raw("date_format(created_at, '%Y-%M')"));
Just use group by with aggregation function.
1 Comment
sr_atiq
not working yet. and loop is needed your formate show me only January values
$year = date("y");
for($i=1; $i<=12; $i++) {
$MonthlyReceive = DB::table('order_items')
->whereBetween('created_at', array($year.'-'.$i.'-1',$year.'-'.$i.'-31'))
->where('quantity', '>', '0')
->sum(DB::raw('price*quantity'));
return $MonthlyReceive;
}
I have fixed it by using this code.
2 Comments
irfanengineer
Great! But try to avoid the loop. I'm also working like this and without loop.
sr_atiq
@er.irfankhan11 Ok I will try to avoid the loop. Thanks for your response.