在Laravel中使用groupBy()函数进行求和查询 [英] Summation query using groupBy() function in laravel

问题描述

其中X，Y和Z具有一些单独数量的表.

A table where X, Y and Z have some individual amounts.

样本数据:

Name | Amount |  Date
—————|————————|—————————
  X  |    100 | 15-11-17
  Y  |     50 | 15-11-17
  X  |     50 | 15-11-17
  Z  |     70 | 15-11-17
  Z  |     30 | 15-11-17

现在，我想显示一个表，其中X将在同一日期返回一行并包含两个值的总和.

Now I want to show a table where X will return one row with the summation of it's two values in the same date.

预期结果:

Name | Amount |  Date
—————|————————|—————————
  X  |    150 | 15-11-17
  Y  |     50 | 15-11-17
  Z  |    100 | 15-11-17

那么laravel查询是什么?我使用groupBy().但是无法获得目标结果.

So what is the laravel query for that? I use groupBy(). But can't get the targeted result.

这是我的laravel查询代码

Here is my laravel query code

$data = DB::table('transactions')                
            ->select('transactions.*')
            ->groupBy('transactions.title_id')
            ->whereDate('transactions.created_at', '=', $date)
            ->get();

并持续出现此错误

"SQLSTATE[42000]: Syntax error or access violation: 1055 'finance_report.transactions.id' isn't in GROUP BY

任何人都可以帮助

推荐答案

假设表名称为transactions，并且列和数据类似于示例表中的列-SQL查询将为

Assuming your table name is transactions, and the columns and data like in your sample table - The SQL query would be

SELECT Name, SUM(Amount) as Amount, Date
FROM transactions
GROUP BY Name, Date

在laravel中，您将其写为

In laravel you would write it as

$data = DB::table('transactions')                
    ->select('Name', DB::raw('SUM(Amount) as Amount'), 'Date')
    ->groupBy('Name', 'Date')
    ->get();

您可以将WHERE条件以及所需的条件添加到查询中.但是，如果需要从表中选择更多列，则还需要将它们添加到groupBy()子句中.由于ONLY_FULL_GROUP_BY模式，诸如transactions.*之类的内容可能无法正常工作.但这也可能没有意义.

You can add your WHERE conditions and what ever you need to the query. But if you need to select more columns from the table, you will also need to add them to the groupBy() clause. Something like transactions.* will probably not work due to ONLY_FULL_GROUP_BY mode. But it also probably doesn't make sense.

这篇关于在Laravel中使用groupBy()函数进行求和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！