[FIXED] How to get total users average time passed 7 days

Issue

I want to get the per day total users average usage time for passed 7 days i written the the SQL for each users average time it's coming perfectly but i have an u\issues in LARAVEL SQL function please help fix this SQL.

$currentTime = Carbon::today();

    $userUsage = DB::table('active_user')
                ->select(DB::raw('acu_name as name'),
                    DB::raw('u_fname as fname'),
                    DB::raw('AVG(TIMESTAMPDIFF(MINUTE,acu_at,acu_et)) as averageTime'),
                    DB::raw('count(*) as number'))
                ->join('u_info_one', 'active_user.acu_name', '=', 'u_info_one.u_email')
                ->whereDate('acu_at', '<=', $currentTime)
                ->groupBy('acu_name')
                ->get();

Solution

You need to use GROUP BY for this, e.g.:

$userUsage = DB::table('active_user')
                ->select(DB::raw('acu_name as name'),
                    DB::raw('u_fname as fname'),
                    DB::raw('AVG(TIMESTAMPDIFF(MINUTE,acu_at,acu_et)) as averageTime'),
                    DB::raw('count(*) as number'))
                ->join('u_info_one', 'active_user.acu_name', '=', 'u_info_one.u_email')
                ->whereDate('acu_at', '<=', $currentTime)
                ->groupBy('acu_name', DB::raw('DATE(acu_at)'))
                ->get();

update

If you just need daily average for all the users, you can remove acu_name from group by, e.g.:

$userUsage = DB::table('active_user')
                ->select(DB::raw('acu_name as name'),
                    DB::raw('u_fname as fname'),
                    DB::raw('AVG(TIMESTAMPDIFF(MINUTE,acu_at,acu_et)) as averageTime'),
                    DB::raw('count(*) as number'))
                ->join('u_info_one', 'active_user.acu_name', '=', 'u_info_one.u_email')
                ->whereDate('acu_at', '<=', $currentTime)
                ->groupBy(DB::raw('DATE(acu_at)'))
                ->get();

Answered By - Darshan Mehta