0

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

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
Arafat
  • 143
  • 1
  • 4
  • 14

2 Answers2

1

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.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • Thanks Paul. It works.... One other question that, can I use any 'date()' function inside 'groupBy()' to convert my date from timestamp to date only ? – Arafat Nov 19 '17 at 05:07
  • Yes, but you will need to use `DB::raw()` like `->groupBy('Name', DB::raw('DATE(Date)'))`. But then you should do the same in the SELECT clause: `->select('Name', DB::raw('SUM(Amount) as Amount'), DB::raw('DATE(Date) as Date'))`. – Paul Spiegel Nov 19 '17 at 05:10
  • Thanks paul. It works again. I didn't use `DB::raw('DATE(Date)')` in `select` part. just only inside the `groupBy()` – Arafat Nov 19 '17 at 05:18
0

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

seravee
  • 434
  • 3
  • 11
  • seravee - according to your query all the amount on same date will sum. But I don't want that. I want all the amount of a same name on a same date will SUM. – Arafat Nov 18 '17 at 04:25
  • Can you try with groupBy('date', 'name') – seravee Nov 18 '17 at 04:27
  • seravee - yes. And i got this error `"SQLSTATE[42000]: Syntax error or access violation: 1055 'finance_report.transactions.id' isn't in GROUP BY` – Arafat Nov 18 '17 at 04:38
  • @Arafat i think issue not related with the above query, have a look at this link, https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error – seravee Nov 18 '17 at 09:35