I have a problem with SQL used on my site: details below
I have a table where users ratings are stored called RATING
On my site I have the following sql statement which works fine for getting the records and displaying them however they are not in the correct rated order.
SELECT * , SUM(rating_value) AS ave FROM rating_values, retailers_rating, retailers, retailers_categories WHERE retailers.publish_date<='2007-09-27' and (retailers.ending_date>='2007-09-27' or retailers.ending_date='0000-00-00') and retailers.available='1' and rating_values.rating_value_id=retailers_rating.rating_value_id and retailers.ret_cat_id = '19' AND retailers_categories.ret_cat_id='19' and retailers.retailer_id = retailers_rating.ret_id GROUP BY retailer_id ORDER BY ave DESC LIMIT 0, 10
I know how to solve the problem just not enough knowledge of SQL to implement it.
where $NUMBER_OF_RATINGS appears in the SQL statement below is where the total number of records against each product stored in ratings table needs to appear
eg 'SELECT * , SUM(rating_value/2) AS ave FROM ....'
SELECT * , SUM(rating_value/$NUMBER_OF_RATINGS ) AS ave FROM rating_values, retailers_rating, retailers, retailers_categories WHERE retailers.publish_date<='2007-09-27' and (retailers.ending_date>='2007-09-27' or retailers.ending_date='0000-00-00') and retailers.available='1' and rating_values.rating_value_id=retailers_rating.rating_value_id and retailers.ret_cat_id = '19' AND retailers_categories.ret_cat_id='19' and retailers.retailer_id = retailers_rating.ret_id GROUP BY retailer_id ORDER BY ave DESC LIMIT 0, 10
the code i use to get the ratings is:-
SELECT COUNT(*) as "NumberRatings" FROM `rating` WHERE `rating_value_id`>=1 group by `p_id` DESC