How to fetch data with sum and group by in Laravel 5.6

Question

I have tow tables (donation and members) i want to fetch member name form member table date and amount from donation table with group by member id form doantion table when i call the function it gives and error like (SQLSTATE[42000]: Syntax error or access violation: 1055 'donatensupport.donations.donation_id' isn't in GROUP BY (SQL: select donations., members., sum(amount) as amount from donations inner join members on donations.member_id = members.id group by member_id)) i google it several times but i did not find any solution any body help thank in advance

Controller

public function index() {
       $donation = DB::table('donations')
            ->join('members', 'donations.member_id', '=', 'members.id')
            ->select('donations.*','members.*', DB::raw('sum(amount) as amount'))
            ->groupBy('member_id')
            ->get();
       return view('donation.index', compact('donation'));
}

Donation Model

public function member() {
    return $this->belongsTo('App\Member','member_id', 'id');
}

Member Model

public function donation() {
    return $this->hasMany('App\Member');
}

Why aren't you using the models?

Jonas Staudenmeir
– Jonas Staudenmeir

2018-04-17 18:13:41 +00:00
Commented Apr 17, 2018 at 18:13 — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Apr 17, 2018 at 18:13

ScaisEdge · Accepted Answer · 2018-04-17 16:16:40Z

2

You can't use donations.* and members.* in select when you are using group by.
In these case you must set explictically the column name you need and make sure that the column name not involved in aggegation function are mentioned in group by clause

   $donation = DB::table('donations')
        ->join('members', 'donations.member_id', '=', 'members.id')
        ->select('donations.member_id as member_id','members.name as name', DB::raw('sum(amount) as amount'))
        ->groupBy('member_id','name')
        ->get();
        return view('donation.index', compact('donation'));

answered Apr 17, 2018 at 16:16

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tarek Adam Over a year ago

I agree it's best to compute as much as you can in sql rather than php. However, I wanted to mention that laravel collections support similar operations, and the line between sql and php can be blurred in a very expressive way that is easy to read months down the road or for the next developer. laravel.com/docs/5.6/collections Depending on how much data there is - collections could be more straight forward and maintainable.

Shahid Hussain Over a year ago

when i add date column it show the date also but the result is not the one which i want

ScaisEdge Over a year ago

if the error is gone then you have found the reason for error .. for " the result you want " seems another qestion to me

Shahid Hussain Over a year ago

there is date column in my donation table also so how to fetch date too with amount and name when i add date column the sum is not working

ScaisEdge Over a year ago

you have error ? the same error in your question?

Collectives™ on Stack Overflow

How to fetch data with sum and group by in Laravel 5.6

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related