2

I need to work in a rating system like this:

enter image description here

I created a table called review like this:

enter image description here

I have tried this query.

 SELECT AVG(`food`) as foodavg FROM review where `restaurant_id` = '10'

But it gives average of only food. and i need to show the result for every restaurant like this.

enter image description here

Please tell me whether this structure is correct. I couldn't figure out a query to show average rating of single restaurant. Will it be using some join, or nested select query?

Please provide any solution to it. Thank you.

Adas
  • 309
  • 1
  • 18
  • to calculate "average", you can use `AVG()` http://www.mysqltutorial.org/mysql-aggregate-functions.aspx – Funk Forty Niner Dec 20 '15 at 15:04
  • yes that i can do but the problem is to run in one query for the same user. like 10 in the table. – Adas Dec 20 '15 at 15:06
  • use a `WHERE` clause then. If you tried something, include it in your question. That link I gave you shows `SELECT AVG(buyPrice) average_buy_price` - `buyPrice` being the column you wish to average. Using an alias is also possible, so maybe you missed using that. – Funk Forty Niner Dec 20 '15 at 15:09
  • see this Q&A http://stackoverflow.com/questions/17004356/creating-5-star-rating-system-with-php-mysql-jquery-and-ajax and http://stackoverflow.com/questions/32160371/php-mysql-star-rating might give you some pointers. There's a link in the OP's question. You can further your research if you want by using these keywords "star rating mysql". – Funk Forty Niner Dec 20 '15 at 15:15
  • Please check as i have change my question, this might help to understand more what i need. thank you – Adas Dec 20 '15 at 15:24

1 Answers1

3

I don't have too much expierience with SQL, but i think what you are looking for is:

SELECT (AVG(`food`)+AVG(`staff`)+AVG(`value`)+AVG(`atmosphere`))/4 FROM review 
where `restaurant_id` = '10'
ArtOfCode
  • 5,702
  • 5
  • 37
  • 56
Philipp
  • 83
  • 4
  • Even i was not sure about the logic if this works in this way for multiple ratings, but i suppose you are absolutely right to get result using your query. – Adas Dec 20 '15 at 15:51
  • It think should be. It is the average from the four ratings you have for one restaurant. – Philipp Dec 20 '15 at 16:12