jeudi 1 novembre 2018

Laravel querying to get the sum of a colon for each month

I am working in a query to calculate the sum of a column for each month and the problem is that when I get the data and one month has no data there is no row for that month and then I don't know which is which month, it would be helpful to return 0 if a months amount is 0, this is the query:

$test = DB::table('actuals')
->Where('company_id',$id)
->Where('account_level_l','LIKE','%Income%')
->select(DB::raw('SUM(amount) as total_amount'))
->groupBy(DB::raw('MONTH(date) ASC'))->get();

and this is the objects that get returned, from 12 months 10 has data:

Collection {#1381 ▼
  #items: array:10 [▼
    0 => {#1370 ▼
      +"total_amount": 133409.46
    }
    1 => {#1371 ▼
      +"total_amount": 77421.36
    }
    2 => {#1372 ▼
      +"total_amount": 78193.81
    }
    3 => {#1373 ▼
      +"total_amount": 940477.66
    }
    4 => {#1374 ▼
      +"total_amount": 81713.1
    }
    5 => {#1375 ▼
      +"total_amount": 64792.43
    }
    6 => {#1376 ▼
      +"total_amount": 85099.41
    }
    7 => {#1377 ▼
      +"total_amount": 127753.07
    }
    8 => {#1378 ▼
      +"total_amount": 62953.11
    }
    9 => {#1379 ▼
      +"total_amount": 356.61
    }
  ]
}



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire