I have a transactions table and I'm trying to get the total of each type.

To simply put it looks like this

I am trying to get sum of each type to show as a report.

top_up: 10000
fee: 50
deduct: 1000
net_expense: 9850 [top_up - deduct - fee]

$types = [
   'top_up' => ['top_up'],
   'deduct' => ['deduct'],
   'fee' => ['fee'],
   'net_expense' => ['top_up', 'deduct', 'fee'], 
];

$query = DB::table('transactions');

foreach ($types as $type => $fields) {

    $query->selectSub(function ($query) use ($fields) {
        return $query->selectRaw('SUM(credit_movement)')->whereIn('type', $fields);
    }, $type);

};

$results = $query->get();

When I do this, I get:

1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'project.transactions.type'; this is incompatible with sql_mode=only_full_group_by..

When I change my database.mysql.strict = false, it works; however I want to make it work properly without needing to change the mysql config.

As of my understanding, this error indicates that I am only selecting aggregated columns, but in my case I don't actually want to groupBy() anything as this is just reports.

If I try to groupBy('type') it returns everything grouped by type, but the queries are only run within that group.

{
 0: {
    top_up: 10000,
    deduct: 0,
    fee: 0,
    net_expense: 10000
 }
 1: {
    top_up: 0,
    deduct: -1000,
    fee: 0,
    net_expense: -1000
 },
 // etc...
}

Is there a way to obtain without changing strict to false?

{
 0 => {
    top_up: 10000,
    deduct: -1000,
    fee: -50,
    net_expense: 9850
 }
}

CodePudding user response：

If I understand you correctly this might be very easy but again I might have not understood it right.

$result = DB::table('transactions')->selectRaw('type, SUM(credit_movement) as sum')->groupBy('status')->get();

This should return something like this:

For the total sum you can just do it in php which would make it easier

$net = $result->sum('sum'); // equals -5656 8758-7625

Hope this helps and let me know if I am wrong about it.

CodePudding user response：

The problem with your approach is in the final column that is the sum of the other 3 so you can't use SUM because you don't have a column to group. You could use a subquery but I think that the best solution is to add a little elaboration of the raw data that you get from a simpler query.

$query = DB::table('transactions')
  ->selectRaw('type, SUM(credit_movement) AS movements')
  ->groupBy('type');

$results = array_reduce($query->get(), function(array $res, array $value){
    $res[$array['type']] = $array['movements'];
    return $res;
}, []);

$results['net_expense'] = array_sum($results);