zq29 Posted August 14, 2007 Share Posted August 14, 2007 I'm trying to pull information from two different tables, using a third for linking. But I want to grab an average of values from one of the tables and I'm getting errors that are telling me I need to use GROUP BY when using AVG() - I'm having difficulties with it! Three tables: `business`, `bc_link`, `feedback` MY QUERY: SELECT b.*, AVG(f.`score`) as `average` FROM `business` as b, `bc_link` as bc, `feedback` as f WHERE bc.`category`='$id' AND bc.`business`=b.`id` AND b.`approved`='1' AND f.`business`=b.`id` ORDER BY b.`premium` DESC, b.`name` ASC Gives the error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause At this point I'm not totaly sure on how I should be using GROUP BY, I'm trying to list all of the businesses listed in a category defined by $id, but also display their average score, where the individual scors are stored in the feedback table. Any help is much appreciated, I could do this with a single query and then another query within a loop, but I'm thinking a single over-all query would be a bit more efficient... Link to comment https://forums.phpfreaks.com/topic/64817-having-difficulty-with-avg-in-a-query-with-table-joins/ Share on other sites More sharing options...
Illusion Posted August 14, 2007 Share Posted August 14, 2007 U need to apply GROUP BY on any of the columns which has the same value for all the records for which ur applying AVG() and also u can retrive those columns which have common value.So ur query need to be modefied. Select group_column1,group_column2,non_group_column_avg from table group by group_column1,group_column2 ; Link to comment https://forums.phpfreaks.com/topic/64817-having-difficulty-with-avg-in-a-query-with-table-joins/#findComment-323411 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.