CODEWITHSUNDEEP
phpmysqleloquentlaravel-5
sr_atiq
sr_atiq

Reputation: 113

Why does aggregation function does not work in my laravel SQL Query?

$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

Upvotes: 1

Views: 402

Answers (2)

sr_atiq
sr_atiq

Reputation: 113

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

Upvotes: 0

irfanengineer
irfanengineer

Reputation: 1300

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.

Upvotes: 2

Related Questions