Sum on Join

Are you looking for a running sum or just a one-time total sum.

@umbus

In eloquent, that would be

    Project::select('*', DB::raw('SUM(transactions.amount) as total'))
        ->join('transactions', 'projects.id', '=', 'transactions.project_id')
        ->get();

https://laracasts.com/discuss/channels/laravel/eloquent-join-two-tables-and-order-by-sum-of-column

I had tried what is on that link but it gave the same error when using the code you gave me:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select *, SUM(transactions.amount) as total from projects inner join transactions on projects.id = transactions.project_id)

Can you help me?

select *, SUM(transactions.amount) as total from projects inner join transactions on projects.id = transactions.project_id

When you run the above SQL in a client, does it give you any error?

If yes, try change to left join:

Project::select('*', DB::raw('SUM(transactions.amount) as total'))
    ->leftJoin('transactions', 'projects.id', '=', 'transactions.project_id')
    ->get();

@laracoft It's not a problem of joins, but normal behavior of group by, because you need to use group by with sum, max etc.

If you use the code I initially gave directly to phpmyadmin, it doesn't give any error

If using leftjoin or join in laravel the error still appears, if I don't use "DB :: Raw" it works but it doesn't make the sum that is what I wanted.

I've tried in many ways but having the two tables together always gives this error ( 1140 Mixing of GROUP columns)

I really don't know what to do

So how do I solve it? The only link between the two tables is projects.id and transactions.project_id

This will work

Project::select('projects.id', DB::raw('SUM(transactions.amount) as total'))
        ->join('transactions', 'projects.id', '=', 'transactions.project_id')
        ->groupBy('projects.id')
        ->get();

So instead of * you have to be clear which columns you want to get.

But in the strict mode everything what you select you have to use in the group by. You can temporary avoid

config(['database.connections.mysql.strict' => false]);

Project::select('projects.id', 'and', 'some', 'other', 'columns', DB::raw('SUM(transactions.amount) as total'))
        ->join('transactions', 'projects.id', '=', 'transactions.project_id')
        ->groupBy('projects.id')
        ->get();

config(['database.connections.mysql.strict' => true]); // only necessary if you have more queries

The first code i dont have errors but only have two attributes: "id" => 1 "total" => 69.0

Can you fix it without having to mess with the database configuration?

Ah.

Project::select('projects.*', DB::raw('SUM(transactions.amount) as total'))
    ->leftJoin('transactions', 'projects.id', '=', 'transactions.project_id')
    ->get();

I don't think groupBy is required when the raw SQL does not need it.

if i use: $projects = Project::select('projects.name', DB::raw('SUM(transactions.amount) as total')) ->join('transactions', 'projects.id', '=', 'transactions.project_id') ->groupBy('projects.id') ->get();

Error: Syntax error or access violation: 1055 'database.projects.name' isn't in GROUP BY (SQL: select projects.description, SUM(transactions.amount) as total from projects inner join transactions on projects.id = transactions.project_id group by projects.id)

@umbus can you confirm the SQL in your OP runs without errors? SELECT projects.* , SUM(transactions.amount) as total FROM projects LEFT JOIN transactions ON projects.id = transactions.project_id

@umbus It's exactly what I describe about strict mode.

Adding a Groupby will affect the value of sum.

1. Groupby projects.id will show many sum, broken down by each project.
2. Without the groupby, it will show the sum of ALL projects.

@umbus are you seeking #1 or #2? groupby is not required to deal with the error.

Yes laracoft works without problems.

MichalOravec I tryed the second code you make dont work too, and if a disable the strict mode in configuration/database dont work too

@laracoft I prefer without groupby, But unfortunately the code that works exactly as I want it is the one I sent initially by placing it directly on phpmyadmin

this code works but dont give me all attributes (only id and total: [code]Project::select('projects.id', DB::raw('SUM(transactions.amount) as total')) ->join('transactions', 'projects.id', '=', 'transactions.project_id') ->groupBy('projects.id') ->get();[/code]

Try this to ensure no errors first:

DB::select(DB::raw(
        'SELECT 
            projects.*,
            SUM(transactions.amount) as total
        FROM projects 
        LEFT JOIN transactions ON projects.id = transactions.project_id'
));

This will work, just add other columns what you need.

config(['database.connections.mysql.strict' => false]);

Project::select('projects.id', 'projects.name', DB::raw('SUM(transactions.amount) as total'))
        ->join('transactions', 'projects.id', '=', 'transactions.project_id')
        ->groupBy('projects.id')
        ->get();

@laracoft dont work have the error: SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: SELECT projects.*, SUM(transactions.amount) as total FROM projects LEFT JOIN transactions ON projects.id = transactions.project_id)

@michaloravec not work have the error: SQLSTATE[42000]: Syntax error or access violation: 1055 'database.projects.name' isn't in GROUP BY (SQL: select projects.id, projects.name, SUM(transactions.amount) as total from projects inner join transactions on projects.id = transactions.project_id group by projects.id)

EDIT (image using phpmyadmin): https://ibb.co/pz2v25p

@umbus If you compare with your opening post, the SQL are the same, so I don't think your OP SQL works. Further, I think you are trying to find the sum for each project, instead of all projects.

DB::select(DB::raw(
        'SELECT 
            projects.*,
            SUM(transactions.amount) as total
        FROM projects 
        LEFT JOIN transactions ON projects.id = transactions.project_id
	GROUP BY projects.id'
));

Because you use strict mode. That's it.

How I said for temporary

config(['database.connections.mysql.strict' => false]);

or set it in config to false, but it's not recomended.

And don't forget to run php artisan config:clear

Check this https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html here you get more info how group by works.

@laracoft Of course he needs to find total per project, for that he needs use group by...

@michaloravec I'm trying not to question his logic, but just solve his groupby. Notice how he clearly prefers option 1 instead of 2 for the numerous times I asked.

@umbus if you want to know the total for all projects, why not just

select sum(transaction.amount) from transactions

Why not just add the project name to the groupBy?

Project::select('projects.id', 'projects.name', DB::raw('SUM(transactions.amount) as total'))
        ->join('transactions', 'projects.id', '=', 'transactions.project_id')
        ->groupBy('projects.id', 'projects.name')
        ->get();

@michaloravec I had already tested "mysql strictfalse", gave the same error. If it is not safe I prefer not to do it

@laracoft dont work =( same error

@sinnbeck Funny had already tried and had not given, with your code gave (finally). The problem is that I have about 20 attributes in the projects table. Isn't there a simpler way to do this like "projects. *"?

But less badly, at least I can do it like this XD

How about a second query to get the items from the projects table and then merge the data? If you are unsure how, I can show you

You are doing something wrong, because if ou set it to false at least temporary is has to work.