mysqli_sql_exception #1111 - Invalid use of group function

Question

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:

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;

Answer 1

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

Answer 2

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

Answer 3

In MySQL if you group by columns, say c1, c2, ..., then those columns can be used as aggregates. In your select clause you cannot use columns directly unless they are aggregates. Non-aggregate columns may still be used as if they were aggregates. With that in mind, let's see your select clause, which I share here unchanged, except for a few newlines that helps one reading it:

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

Since you grouped by kelas, it can be used directly. From this perspective, count(kelas) is misleading, you could use count(*) instead. But this is not the cause of your error. The cause of your error is that you are using non-aggregate fields as if they were aggregates, notably:

• area
• majorAxis
• minorAxist
• eccentricity
• convexArea
• extent
• perimeter

To illustrate, let me give you an example:

pow(data_training.area-avg(area),2)/(count(kelas)-1)

Here, data_training.area is a non-aggregate column used as if it was an aggregate column. We have already grouped by kelas, but not by area, so what would area mean in this case, especially if there are multiple possible values. avg(area) makes sense, it means that we take all the values of area from the group and compute their average. So, data_training.area cannot be used by itself, whereas avg(area) makes perfect sense.

I do not know what you wanted to compute with your query, so I do not know what your solution will be unless you provide further contextual information, but it's perfectly clear that the cause of the specific error you have mentioned is that you treat non-aggregates as if they were aggregates.