Jump to content

Having difficulty with AVG() in a query with table joins


zq29

Recommended Posts

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...

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 ;

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.