Joining table in mysql

Question

I am developing a business review system. business table is-

id     category
1         1
2         1
3         1
4         2

review table is -

id     bid     reviewer_point
1       1             4
2       1             3
3       2             4
4       2             5

I need to return the 10 business ID's of the top rated (based on rating point average) business filtered by category.

So far i could find the average of the rating of each business.

SELECT business.category, bid, 
     COUNT(*) AS review_count, AVG(reviewer_point) AS average_points 
FROM reviews 
GROUP BY bid    
ORDER BY average_points DESC WHERE category = 1 LIMIT 10;

I am unable to use WHERE clause. How can i get my preferred solution. Thanks in advance

Answer 1

The WHERE clause has to come before the GROUP BY

SELECT business.category, bid, 
     COUNT(*) AS review_count, AVG(reviewer_point) AS average_points 
FROM review
WHERE category = 1 
GROUP BY bid    
ORDER BY average_points DESC
LIMIT 10;

However, this still won't work as the business table hasn't been joined in this query. I'd include this but there's no obvious way to join business to review. Should the review table have a column containing a reference to business.id?

SQLFiddle here

Best of luck.

EDIT

With the information supplied in a comment (below) by OP the revised query becomes

SELECT b.category, r.bid, 
     COUNT(*) AS review_count, AVG(reviewer_point) AS average_points 
FROM review r
INNER JOIN business b
  ON b.id = r.bid
WHERE b.category = 1 
GROUP BY r.bid, b.category
ORDER BY average_points DESC
LIMIT 10;

and the updated SQLFiddle can be found here

Answer 2

The order in a mysql single query is:

1st SELECT;
2nd FROM;
3rd WHERE;
4th GROUP BY;
5th HAVING;
6th ORDER BY;
7th LIMIT.

Try put each with a break line.