How to group by 2 mobile number columns as 1 with many average and sum logic in the select statement in PHP Laravel MySQL

I’ve been trying to do this since last week but still no luck.

Here’s my sample data: enter image description here

What I want is to get average of distance, min of min distance, max of max distance, and sum the interaction duration by difference between interaction start and end, then group them by mobile number 1 and 2, but I want to make it like if the pair of mobile number 1 and 2 exist already doesn’t matter which one is mobile number 1 and which one is mobile number 2, it should be combined together

What I tried using eloquent is this:

$interaction_list = Interaction::select(
DB::raw('ROUND(latitude, 6) as latitude'),
DB::raw('ROUND(longitude, 6) as longitude'),
DB::raw('ROUND(AVG(distance), 2) as distance'),
DB::raw('MIN(ROUND(min_distance, 2)) as min_distance'),
DB::raw('MAX(ROUND(min_distance, 2)) as max_distance'),
DB::raw('SUM(TIMESTAMPDIFF(SECOND, interaction_started_at, interaction_ended_at)) as duration'),
)->groupBy('mobile_no_1', 'mobile_no_2')
->orderBy($sort, $order)

And this is the result that I get: enter image description here

Tracked User = mobile_no_1, Interaction = mobile_no_2

For the avg, min, max, and sum already correct but I want to make above result to be 1 row only for this data sample with duration would be 10 + 30 = 40 since it’s combined into 1 row.

I was able to get the result by using distinct() before select() instead of groupBy() but then after getting other data in the table it becomes inaccurate since there’s interaction of mobile number 01234567892 with 0111111111, and the query combine that record together as 01234567892 with 01234567893 hence the result of record 01234567892 with 01111111 never appear in the table so I believe it’s wrong approach already.

from Newest questions tagged laravel-5 – Stack Overflow

Related Posts

Codeigniter : Parse error: syntax error, unexpected ‘const’ (T_CONST), expecting variable (T_VARIABLE) in Laravel project

I’m getting following error: **Parse error: syntax error, unexpected ‘const’ (T_CONST), expecting variable (T_VARIABLE)** Note : It’s working in local but facing issue in production server. private…

Firebase receive notification while tab is active or on focus

What i want is to be able to perform an action when a user receives a notification while the browser is open and tab is active or…

Laravel’s alias loader does not find class

We have a legacy project that we cannot update and we need to make some changes in symfony’s Response.php in vendor. We have solved this by copying…

Laravel 5 – generic document management

I have a system where you can create different types of unique documents. For instance, one document is called Project Identified and this expects certain inputs. Originally,…

Laravel Nova limit the results in indexQuery

I ran intro a situation where I need to limit the results of a resource to only 3 results. To be more specific, based on the logged…

Auditoria en laravel 5.8 [closed]

Cómo puedo automatizar el registro de actividades de un usuario en laravel? Si un usuario ingresa a un app de laravel, debo guardar toda su actividas, a…

Leave a Reply

Your email address will not be published.