CODEWITHSUNDEEP
phpmysqllaravel-5.3
Arafat
Arafat

Reputation: 143

Summation query using groupBy() function in laravel

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

Sample data:

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

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.

Expected result:

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

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

Here is my laravel query code

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

And got this error continuously 

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

Anybody please help

Upvotes: 0

Views: 239

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31802

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

In laravel you would write it as 

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

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.

Upvotes: 1

seravee
seravee

Reputation: 434

You can use SUM(Amount) with groupBy(date)

Upvotes: 0

Related Questions