Laravel Group By和另一列的总和 [英] Laravel Group By and Sum total value of other column

问题描述

我的数据库中有一个表，如下所示:

I have a table in my database as shown below:

id | date       | amount
========================
1  | 2015-01-26 | 1000
2  | 2015-02-26 | 100
3  | 2014-06-26 | 10

我想按年份对记录进行分组，然后找到每个组的金额之和.

I want to group the records by year and then find the sum of the amount of each group.

我可以使用以下查询找到这些组:

I can find the groups using the following query:

Fee::orderBy('date', 'DESC')
  ->get()
  ->groupBy(function($date) {
    return Carbon::parse($date->date)->format('Y'); // grouping by years
})

但是我无法获得每个组的总金额.

But I am not able to get the sum of amount of each group.

推荐答案

怕我现在无法雄辩地解决这个问题，尽管这对于查询生成器应该有效.对我来说还可以:

Afraid I can't figure it out right now with eloquent, this should work for the query builder though. It's working okay for me:

DB::table('fees')
    ->select(DB::raw('YEAR(date) as year'), DB::raw('sum(amount) as total'))
    ->groupBy(DB::raw('YEAR(date)') )
    ->get();

使用您在问题中输入的同一日期:

Using the same date you put in the question:

id       date                       amount
 1       2015-01-26                  1000
 2       2015-02-26                  100
 3       2014-06-26                  10

它给出了以下内容:

array:2 [▼
  0 => {#150 ▼
    +"year": 2014
    +"total": "10"
  }
  1 => {#151 ▼
    +"year": 2015
    +"total": "1100"
  }
]

这篇关于Laravel Group By和另一列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！