-1

My query (DB::raw(AVG('pt.progress'))) this part is throwing error at the moment

$query = DB::table('projects as p')
    ->leftJoin('projects_tasks as pt','pt.project_id', 'p.id')
    ->select(
        'p.id', 'p.project_name', DB::raw(AVG('pt.progress')) //this is where I need the average
    );
$query->orderBy($order, $dir);
if ($limit != -1) {
    $query->skip($start)->take($limit);
}
$records = $query->get();

Table structure:

projects:
========
id
project_name
...
...

projects_tasks:
===============
id
project_id,
parent, //0 or 1 
progress //exmaple value 0.00 to 1.00

How to get the average of progress, where parent_id = 0 and project_id is the same?

The following query does work if I create a function and pass it in a loop, however, I want to optimize it and run it by joining on above query.

$data_row = DB::table('projects_tasks')
     ->select(DB::raw('(SUM(progress)*100)/count(progress) as project_progress'))
     ->where(['project_id' => $project_id, 'parent' => 0])
     ->get();
Aayush Dahal
  • 856
  • 1
  • 17
  • 51

1 Answers1

0

Seems that you have a syntax error on your query, the problem is in here DB::raw(AVG('pt.progress')).

Since you're using a raw query, the parameter there should be a string, so you must enclose that in quotes / double qoute.

$query = DB::table('projects as p')
    ->leftJoin('projects_tasks as pt','pt.project_id', 'p.id')
    ->select(
        'p.id', 'p.project_name', DB::raw("AVG('pt.progress')")
    );
$query->orderBy($order, $dir);
if ($limit != -1) {
    $query->skip($start)->take($limit);
}
$records = $query->get();
aceraven777
  • 4,358
  • 3
  • 31
  • 55
  • `In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'database.p.id'; this is incompatible with sql_mode=only_full_group_by (SQL: select `p`.`id`, `p`.`project_name`, AVG('pt.progress') from `projects` as `p` left join `projects_tasks` as `pt` on `pt`.`project_id` = `p`.`id` order by `id` desc limit 25 offset 0)` Could be version mismatch? also, I don't want to get avg from all rows, I want to get average from only those rows, whose `'parent' => 0` – Aayush Dahal Dec 01 '22 at 02:06
  • If you've Googled the error, you've found out why that is happening. That's showing because in your MySQL settings in your `sql_mode`, you have enabled `ONLY_FULL_GROUP_BY`. In order to make this work, you have to remove that. See solution here: https://stackoverflow.com/questions/23921117/disable-only-full-group-by – aceraven777 Dec 01 '22 at 03:22
  • For a more permanent solution, I recommend you to remove the `ONLY_FULL_GROUP_BY` via `my.cnf` or `my.ini` – aceraven777 Dec 01 '22 at 03:23