My groupBy is not grouping the data with 2 columns

Hi @jonjie

What is your expected result?

With the info supplied for the result, is not enough to tell that is not working. I don't see repeating transaction_id fields so it seems to be grouping by that field.

Same for store_id... the IDs 32 & 34 shows up twice, but for different transaction_id which is correct because you are first grouping by transaction_id and then by store_id.

EDITED: By the way, according to documentation the correct way would be

->groupBy('transaction_id', 'store_id')

Doc: https://laravel.com/docs/7.x/queries#ordering-grouping-limit-and-offset

@marianomoreyra I want it to group by the transaction_id and store_id as well, but it returns with duplicate store_ids. I also tried to switch between those 2 columns but still the same result.

@jonjie if you group in that order, as I explained it will duplicate store_ids because they belong to different groups (in this case, different transaction_id).

Now if you switch the order, you shouldn't have store_id duplicated, but maybe transaction_id (if it's possible to have the same transaction_id for different store_ids)

Please, try switching again and share the results here. Remember to use:

->groupBy('store_id', 'transaction_id')

@marianomoreyra When I switch the 2 columns, it returns this result:

0	Object { store_id: 32, transaction_id: "COD1000145148", … }
1	Object { store_id: 32, transaction_id: "COD1000145149", … }
2	Object { store_id: 33, transaction_id: "COD1000145150", … }
3	Object { store_id: 34, transaction_id: "COD1000145151", … }
4	Object { store_id: 34, transaction_id: "COD1000145152", … }
5	Object { store_id: 35, transaction_id: "COD1000145153", … }

@jonjie check the query after executing with the following change:

DB::table('orders')
		->join('store', 'order.store_id', 'store.id')
            	->whereDate('order_date', '>=', $from)
            	->whereDate('order_date', '<=', $to)
            	->select(
                [
                	'store_id',
                	'transaction_id',
                	'currency',
                	'store_name',
			DB::raw('SUM(grand_total) as total')
                ]
            )
            ->groupBy('store_id', 'transaction_id')
	    ->dd();

Post the result so we can try to help you

@marianomoreyra It only returns 2 rows now.

select `store_id`, `transaction_id`, `currency`, `store_name`, `SUM(ord_grant_total) as total` where date(`order_date`) >= ? and date(`order_date`) <= ? group by `store_id`, `transaction_id`

array:2 [
  0 => "2020-01-01"
  1 => "2020-08-07"
]

@marianomoreyra please see my update. Sorry, I accidentally remove the part of DB::raw(COUNT(DISTINCT(transaction_id)) as orders_count) when I pasted the code. Maybe this is the reason?

@jonjie I don't know why aren't you getting any errors, as I don't see the from orders neither the join part of the query there.

Also, at the join clause you are using order.store_id while based on the table name should be orders.store_id right?

Also, you have fields at the select clause that are not included at the group by and are not aggregated either...

What do you get if you correct the table at the join and temporary remove the select? I'd add the operator for the join too, just in case:

DB::table('orders')
		->join('store', 'orders.store_id', '=', 'store.id')
            	->whereDate('order_date', '>=', $from)
            	->whereDate('order_date', '<=', $to)
            ->groupBy('store_id', 'transaction_id')
	    ->dd();