how select sum from 2 tables group by another table

You can can create query like this.

• Find cash_in and cash_out as below.

$cash = DB::table('cash_in_out')
      ->groupBy('supervises_id');

$cashIn = $cash->('cash_in');

$cashOut  = $cash->('cash_out');

• Find sum of price as below

$price = DB::table('sepnd_info')
      ->groupBy('supervises_id')
    ->sum('price');

thanks for your help

but what i need get result as one array

i have done this

$spduties = DB::select('select id, SUM(cash_in) as cash_in_sum,SUM(cash_out) as cash_out_sum,SUM(price) as sum_price
                            from (
                               
                                select id ,0 as cash_in,0 as cash_out,0 as price
                                from supervisors

                                union
                                select person_id,cash_in,cash_out,0 as price 
                                from cash_flows ts

                                union all
                                select supervisor_id, 0 as cash_in,0 as cash_out, price
                                from spduties
                            ) sc
                            where id != 0
                            group by id');

and get this result

[
{
"id": 1,
"cash_in_sum": 87936.15,
"cash_out_sum": 71845,
"sum_price": 3150
},
{
"id": 2,
"cash_in_sum": 68740,
"cash_out_sum": 239120,
"sum_price": 0
},
{
"id": 3,
"cash_in_sum": 0,
"cash_out_sum": 0,
"sum_price": 0
},
{
"id": 4,
"cash_in_sum": 0,
"cash_out_sum": 0,
"sum_price": 0
}
]

but the problem that i need to get user name witch call "sv_name" in supervisors table

the query have to be like this

$spduties = DB::select('select id,sv_name, SUM(cash_in) as cash_in_sum,SUM(cash_out) as cash_out_sum,SUM(price) as sum_price
                            from (
                               
                                select id ,sv_name,0 as cash_in,0 as cash_out,0 as price
                                from supervisors

                                union
                                select person_id,0 as sv_name, cash_in,cash_out,0 as price 
                                from cash_flows ts

                                union all
                                select supervisor_id,0 as sv_name,  0 as cash_in,0 as   cash_out, price
                                from spduties
                            ) sc
                            where id != 0
                            group by id');

this query run without any problem in phpmyadmin

but in query bullder i get this error

SQLSTATE[42000]: Syntax error or access violation: 1055 'sc.sv_name' isn't in GROUP BY (SQL: select id,sv_name, SUM(cash_in) as cash_in_sum,SUM(cash_out) as cash_out_sum,SUM(price) as sum_price
from (

select id ,sv_name,0 as cash_in,0 as cash_out,0 as price
from supervisors

union
select person_id,0 as sv_name, cash_in,cash_out,0 as price 
from cash_flows ts

union all
select supervisor_id,0 as sv_name, 0 as cash_in,0 as cash_out, price
from spduties
) sc
where id != 0
group by id)

any idea ?

thanks

any help please ?

Maybe if you add this sv_name to the group clause like:

group by id, sv_name

it should do the trick?

thanks @tekmi

but this give me duplicate result

[
{
"id": 1,
"sv_name": "0",
"cash_in_sum": 87936.15,
"cash_out_sum": 71845,
"sum_price": 3150
},
{
"id": 1,
"sv_name": "user1",
"cash_in_sum": 0,
"cash_out_sum": 0,
"sum_price": 0
},
{
"id": 2,
"sv_name": "0",
"cash_in_sum": 68740,
"cash_out_sum": 239120,
"sum_price": 0
},
{
"id": 2,
"sv_name": "user2",
"cash_in_sum": 0,
"cash_out_sum": 0,
"sum_price": 0
},
{
"id": 3,
"sv_name": "user3",
"cash_in_sum": 0,
"cash_out_sum": 0,
"sum_price": 0
},
{
"id": 4,
"sv_name": "user4",
"cash_in_sum": 0,
"cash_out_sum": 0,
"sum_price": 0
}
]

Hmm, then looks like your first query could have been actually good (the one with only grouping by id).

Maybe the problem is with the default SQL Mode in MySQL (assuming that you use this database): https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

If not, then the most dirty solution would be to go through those duplicates in PHP and group them...

Or maybe somehow force the cash_flows.sv_name and spduties.sv_name to be the same as supervisors.sv_name (if I understand it correctly, the supervisors table has this column present, the other tables just use the dummy 0 value)

@tekmi

thanks so much fiend you drive me where to find the solution

query actually act diffract on live server

but generally i made change on "config/database.php"

'strict' => false,

did you know if this effect the security ?

this the final query

$cash_flows = DB::select('select id,sv_name, SUM(cash_in) as cash_in_sum,SUM(cash_out) as cash_out_sum,SUM(price) as sum_price
                            from (
                               
                                select id,sv_name,0 as cash_in,0 as cash_out,0 as price
                                from supervisors
                
                                union 
                                select person_id,0 as sv_name, cash_in,cash_out,0 as price 
                                from cash_flows ts

                                union 
                                select supervisor_id,0 as sv_name,  0 as cash_in,0 as cash_out, price
                                from spduties
                            ) sc
                            where id != 0
                            group by id');

thanks so much again

this works only on live server , maybe because mysql version

@amrsoft I'm happy I could help. I think the newest MySQL version has different default sql mode set, therefore causing some headaches.

In regards to your question about strict configuration. I think it may have some side-effects.

Please check out this part of Laravel Framework code: https://github.com/laravel/framework/blob/5.4/src/Illuminate/Database/Connectors/MySqlConnector.php#L178 which actually processes the strict configuration option.

In my opinion you should just get rid of mode ONLY_FULL_GROUP_BY, the rest should stay as it was.

Looks like it's possible with the modes configuration option, according to this part: https://github.com/laravel/framework/blob/5.4/src/Illuminate/Database/Connectors/MySqlConnector.php#L146

Good luck.