phpmyadmin Question posted in
The official documentation can be found here.

How to get total users average time passed 7 days – Phpmyadmin

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 uissues 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();

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Correct Answer is 

        $userUsage = DB::table('active_user')
                ->select(DB::raw('DATE(acu_at) as date'),
                    DB::raw('AVG(TIMESTAMPDIFF(MINUTE,acu_at,acu_et)) as averageTime'),
                    DB::raw('COUNT(DISTINCT `acu_name`) as users'))
                ->join('u_info_one', 'active_user.acu_name', '=', 'u_info_one.u_email')
                ->whereDate('acu_at', '<=', $currentTime)
                ->groupBy(DB::raw('DATE(acu_at)'))
                ->orderBy(DB::raw('DATE(acu_at)'))
                ->get();

foreach($userUsage as $usUa)
   {
     $avegTime = ($usUa->averageTime/$usUa->users);
     echo "['".$usUa->date."',".$avegTime."],";
   }

  2. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search