Try this out!!

[jacquesy]

I am creating a website for digital camera and want to create a top5 table of digital camera manufacturers which is worked out by the rating given to each manufacturers model.

I have a manufacturer table and a model table that are joined using manufacturer_id.

Each model has a rating out of 100.

To get this to work I will need to count how many models each manufacturers has and divide this by the total of each model_rating added together. This will work out the average rating

I will then need to go on to order each manufacturer by the highest average rating.

Can anybody help me??

😕

[khaldryck]

try this query :

i guess the avg() mysql function can help, but i never used it,
maybe it would be best read the doc about that.

$query="select manufaturers.id, manufacturers.name, sum(models.rating)/count(models.id) as average from manufacturers inner join models on models.manufacturer_id=manufacturers.id group by manufacturers.id,  manufacturers.name
order by average";

[barand]

My 0.02 worth as a minor improvement

$query="select manufaturers.id, manufacturers.name, sum(models.rating)/count(models.id) as average from manufacturers inner join models on models.manufacturer_id=manufacturers.id group by manufacturers.id,  manufacturers.name
order by average DESC LIMIT 5";