Question 72340 in Mysql Question posted in
The official MySQL documentation can be found here.

mysqli_sql_exception #1111 – Invalid use of group function

I am using codeigniter 4. I want to sum this data from my model.

pow(data_training.area-avg(area),2)

so I type like this :

sum(pow(data_training.area-avg(area),2))

but it’s turn out like this:
error screenshot

Please tell me how to sum the data values. Here is my full code :

public function getVariance() {
     return $this->db->table('data_training')
     ->select('pow(data_training.area-avg(area),2)/(count(kelas)-1) as areaVariance, pow(majorAxis-avg(majorAxis),2)/(count(kelas)-1) as majorAxisVariance, pow(minorAxis-avg(minorAxis),2)/(count(kelas)-1) as minorAxisVariance, pow(eccentricity-avg(eccentricity),2)/(count(kelas)-1) as eccentricityVariance, pow(convexArea-avg(convexArea),2)/(count(kelas)-1) as convexAreaVariance, pow(extent-avg(extent),2)/(count(kelas)-1) as extentVariance, pow(perimeter-avg(perimeter),2)/(count(kelas)-1) as perimeterVariance')
     ->groupBy('kelas')
     ->get()->getResultArray();
    }

and here is my query that worked. So I tried to translate it to php code but i don’t know how.

select sum(pow(dtr.area-atr.area,2))/(jml-1) as AreaVariance, sum(pow(dtr.majorAxis-atr.majorAxis,2))/(jml-1) as majorAxisVariance, sum(pow(dtr.minorAxis-atr.minorAxis,2))/(jml-1) as minorAxisVariance, sum(pow(dtr.eccentricity-atr.eccentricity,2))/(jml-1) as eccentricityVariance, sum(pow(dtr.convexArea-atr.convexArea,2))/(jml-1) as convexAreaVariance, sum(pow(dtr.extent-atr.extent,2))/(jml-1) as extentVariance, sum(pow(dtr.perimeter-atr.perimeter,2))/(jml-1) as perimeter, dtr.kelas from data_training dtr join ( select count(area) as jml,avg(area) as area, avg(majorAxis) as majorAxis, avg(minorAxis) as minorAxis, avg(eccentricity) as eccentricity, avg(convexArea) as convexArea, avg(extent) as extent, avg(perimeter) as perimeter, kelas from data_training group by kelas) atr on dtr.kelas = atr.kelas group by dtr.kelas;
Tags:

2

Answers


  1. 0

    You can use subqueries.
    You can rewrite your getVariance() function:

    public function getVariance() {
    $subquery = $this->db->table('data_training')
        ->select('count(area) as jml, avg(area) as area, avg(majorAxis) as majorAxis, avg(minorAxis) as minorAxis, avg(eccentricity) as eccentricity, avg(convexArea) as convexArea, avg(extent) as extent, avg(perimeter) as perimeter, kelas')
        ->groupBy('kelas');

    return $this->db->table('data_training dtr')
        ->select('sum(pow(dtr.area-atr.area,2))/(jml-1) as AreaVariance, sum(pow(dtr.majorAxis-atr.majorAxis,2))/(jml-1) as majorAxisVariance, sum(pow(dtr.minorAxis-atr.minorAxis,2))/(jml-1) as minorAxisVariance, sum(pow(dtr.eccentricity-atr.eccentricity,2))/(jml-1) as eccentricityVariance, sum(pow(dtr.convexArea-atr.convexArea,2))/(jml-1) as convexAreaVariance, sum(pow(dtr.extent-atr.extent,2))/(jml-1) as extentVariance, sum(pow(dtr.perimeter-atr.perimeter,2))/(jml-1) as perimeter, dtr.kelas')
        ->join("({$subquery->getCompiledSelect()} atr)", 'dtr.kelas = atr.kelas', 'left')
        ->groupBy('dtr.kelas')
        ->get()
        ->getResultArray();
}
    Login or Signup to reply.
  2. 0

    try this one

    public function getVariance() {
$subquery = $this->db->table("data_training")
    ->select("kelas, COUNT ( kelas ) kelas_count, SUM ( area ) sum_area, AVG ( area ) avg_area, SUM ( majoraxis ) sum_majoraxis, AVG ( majoraxis ) avg_majoraxis, SUM ( minoraxis ) sum_minoraxis, AVG ( minoraxis ) avg_minoraxis, SUM ( eccentricity ) sum_eccentricity, AVG ( eccentricity ) avg_eccentricity, SUM ( convexarea ) sum_convexarea, AVG ( convexarea ) avg_convexarea, SUM ( extent ) sum_extent, AVG ( extent ) avg_extent, SUM ( perimeter ) sum_perimeter, AVG ( perimeter ) avg_perimeter")
    ->groupBy("kelas");

return $this->db->newQuery()->select("POW( ( sum_area - avg_area ), 2 ) / ( kelas_count - 1 ) AS areaVariance, POW( ( sum_majoraxis - avg_majoraxis ), 2 ) / ( kelas_count - 1 ) AS majorAxisVariance, POW( ( sum_minoraxis - avg_minoraxis ), 2 ) / ( kelas_count - 1 ) AS minorAxisVariance, POW( ( sum_eccentricity - avg_eccentricity ), 2 ) / ( kelas_count - 1 ) AS eccentricityVariance, POW( ( sum_convexarea - avg_convexarea ), 2 ) / ( kelas_count - 1 ) AS convexAreaVariance, POW( ( sum_extent - avg_extent ), 2 ) / ( kelas_count - 1 ) AS extentVariance, POW( ( sum_perimeter - avg_perimeter ), 2 ) / ( kelas_count - 1 ) AS perimeterVariance")->fromSubquery($subquery, 'dt')->get()->getResultArray();

    }

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search