Error #1111: Invalid use of group function for the query

[jetic83]

Thank you for this nice plugin! I added it to my mantis. But unfortunately, I cannot creat the gauge data.

in issue_ranking.php, the query

$dbquery = "SELECT max(sd.bugid) as bugid, count(sd.rating) as no_of_ratings, sum(sd.rating) as sum_of_ratings, avg(sd.rating) as avg_rating, max(sd.rating) as highest_rating, min(sd.rating) as lowest_rating, IFNULL(bm2_count,0) AS bm2_count, IFNULL(bm2_sum,0) AS bm2_sum, IFNULL(bm1_count,0) AS bm1_count, IFNULL(bm1_sum,0) AS bm1_sum, IFNULL(b2_count,0) AS b2_count, IFNULL(b2_sum,0) AS b2_sum, IFNULL(b1_count,0) AS b1_count, IFNULL(b1_sum,0) AS b1_sum FROM {$plugin_table} sd INNER JOIN {$bug_table} b ON sd.bugid = b.id LEFT OUTER JOIN (SELECT bugid, count(rating) as bm2_count, sum(rating) as bm2_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = -2) bm2 ON sd.bugid = bm2.bugid LEFT OUTER JOIN (SELECT bugid, count(rating) as bm1_count, sum(rating) as bm1_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = -1) bm1 ON sd.bugid = bm1.bugid LEFT OUTER JOIN (SELECT bugid, count(rating) as b2_count, sum(rating) as b2_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = 2) b2 ON sd.bugid = b2.bugid LEFT OUTER JOIN (SELECT bugid, count(rating) as b1_count, sum(rating) as b1_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = 1) b1 ON sd.bugid = b1.bugid {$where_clause} GROUP BY sd.bugid ORDER BY sum(sd.rating) {$order} LIMIT {$start}{$noOfBugs}";

results in the error "Database query failed. Error received from database was #1054: Unknown column 'rating' in 'having clause' for the query".

When I add the column "rating", I still get an other error. New query:

$dbquery = "SELECT max(sd.bugid) as bugid, count(sd.rating) as no_of_ratings, sum(sd.rating) as sum_of_ratings, avg(sd.rating) as avg_rating, max(sd.rating) as highest_rating, min(sd.rating) as lowest_rating, IFNULL(bm2_count,0) AS bm2_count, IFNULL(bm2_sum,0) AS bm2_sum, IFNULL(bm1_count,0) AS bm1_count, IFNULL(bm1_sum,0) AS bm1_sum, IFNULL(b2_count,0) AS b2_count, IFNULL(b2_sum,0) AS b2_sum, IFNULL(b1_count,0) AS b1_count, IFNULL(b1_sum,0) AS b1_sum FROM {$plugin_table} sd INNER JOIN {$bug_table} b ON sd.bugid = b.id LEFT OUTER JOIN (SELECT bugid, rating, count(rating) as bm2_count, sum(rating) as bm2_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = -2) bm2 ON sd.bugid = bm2.bugid LEFT OUTER JOIN (SELECT bugid, rating, count(rating) as bm1_count, sum(rating) as bm1_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = -1) bm1 ON sd.bugid = bm1.bugid LEFT OUTER JOIN (SELECT bugid, rating, count(rating) as b2_count, sum(rating) as b2_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = 2) b2 ON sd.bugid = b2.bugid LEFT OUTER JOIN (SELECT bugid, rating, count(rating) as b1_count, sum(rating) as b1_sum FROM {$plugin_table} GROUP BY bugid, rating HAVING rating = 1) b1 ON sd.bugid = b1.bugid {$where_clause} GROUP BY sd.bugid ORDER BY sum(sd.rating) {$order} LIMIT {$start}{$noOfBugs}";

New Error: "Database query failed. Error received from database was #1111: Invalid use of group function for the query"

Any suggestion?

[jetic83]

The problem was solved by replacing

ORDER BY sum(sd.rating) {$order}

by

ORDER BY sum_of_ratings {$order}

(order by a function cannot be done).

[EvilRenegade]

That sounds more like the plugin wasn't properly initiated. The column rating should've been created as part of the database schema: https://github.com/EvilRenegade/Gauge-Support/blob/master/GaugeSupport.php#L80