php 按月生成数据表,php - PHP Laravel按月和年从数据库中提取数据

您好，我有此功能，该功能可将budget_cost求和并按月获取数据public function marktingCost(){

$costs = \DB::table('campaigns')

->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month'))

->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year'))

->groupBy('campaign_leadsource_id')

->where('campaign_status_id',4) // campaign_status_id = 4 means campaign completed

->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month)

->get(); return $costs}

我试图达到的目标是按月将数据作为budget_total_month并按年将数据作为budget_total_year

但是我无法使用查询中的条件是否想要做这样的事情

->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month') ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month))

->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year') ->where(\DB::raw('Year(created_at)'), Carbon::today()->year))

但是那当然是无效的

我想要的输出是

[{"campaign_leadsource_id":1,"budget_total_month":11475,"budget_total_year":134761,"olxTotal":12,"budget_per_lead":11230},{"campaign_leadsource_id":2,"budget_total_month":4221,"budget_total_year":41215,"olxTotal":9,"budget_per_lead":4579}]

先感谢您

最佳答案

请尝试此代码。$costs = \DB::table('campaigns')

->select('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_month'))

->addselect('campaign_leadsource_id', \DB::raw('SUM(budget_cost) as budget_total_year'))

->where('campaign_status_id',4) // campaign_status_id = 4 means campaign completed

->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month)

->groupBy(\DB::raw("MONTH(created_at)"))

->get();