CODEWITHSUNDEEP
phplaravellaravel-5
Yngvar Kalvø
Yngvar Kalvø

Reputation: 35

How do I group values in array by month?

I'm trying to add sum all values of a specific key to an array devided into months, but can't figure out how to do it. I manage to get the first value, but trying to add on to that only gives me errors.

$accumulatedMonthly = DB::table('sold_tickets')
    ->select('price', 'created_at')
    ->where('event_id', $id)
    ->where('credited', null)
    ->where('user_id', '!=', null)
    ->orderBy('created_at')
    ->get()
    ->groupBy(function($val) {
        return Carbon::parse($val->created_at)->format('M y');
    });

$accumulatedMonthly = json_decode(json_encode($accumulatedMonthly), true);
$accumulatedPerMonth = [];

foreach ($accumulatedMonthly as $k => $month) {
    foreach ($month as $m) {
        $accumulatedPerMonth[$k] = $m['price'];
    }
}

I would like the result to be devided into months with all the 'price'-values added on top of eachother. Now I get the months correctly, but only the first value of each month.

This is the output currently

Array
(
    [Aug 16] => 999
    [Nov 16] => 1399
    [Dec 16] => 1399
    [Jan 17] => 1399
    [Feb 17] => 1599
    [Mar 17] => 1599
    [Apr 17] => 1599
    [May 17] => 1599
    [Jun 17] => 1599
    [Jul 17] => 1599
    [Aug 17] => 1199
)

Upvotes: 2

Views: 95

Answers (3)

umefarooq
umefarooq

Reputation: 4574

try collection pluck method, you will have array data.

update i have modified query.

$accumulatedMonthly = DB::table('sold_tickets')
    ->select(DB::raw('SUM("price") as price'), DB::raw("date_format('created_at','%M %y') as month"))
    ->where('event_id', $id)
    ->where('credited', null)
    ->where('user_id', '!=', null)
    ->orderBy('created_at')
    ->get()
    ->groupBy(DB::raw("date_format('created_at','%M %y')"))->pluck('price','month');

Upvotes: 1

Saleemi
Saleemi

Reputation: 131

You can do something like this:

    $accumulatedMonthly = DB::table('sold_tickets')
    ->select('price', 'created_at')
    ->where('event_id', $id)
    ->where('credited', null)
    ->where('user_id', '!=', null)
    ->orderBy('created_at')
    ->get();

     $accumulatedPerMonth = array();

     foreach ($accumulatedMonthly as $key => $value) 
     {
         if(array_key_exists(date('M y', strtotime($value->created_at)), $accumulatedPerMonth))
         {
             $accumulatedPerMonth[date('M y', strtotime($value->created_at))] += $value->price;  
         }
         else
         {
             $accumulatedPerMonth[date('M y', strtotime($value->created_at))] = $value->price; 
         }
     }

Upvotes: 0

markmoxx
markmoxx

Reputation: 1542

Change 

foreach ($accumulatedMonthly as $k => $month) {
    foreach ($month as $m) {
        $accumulatedPerMonth[$k] = $m['price'];
    }
}

to: 

foreach ($accumulatedMonthly as $k => $month) {
    $accumulatedPerMonth[$k] = 0;
    foreach ($month as $m) {
        $accumulatedPerMonth[$k] += $m['price'];
    }
}

to get a sum of all the prices.

Upvotes: 2

Related Questions