MySQL Table Column Average

[ShoeLace1291]

I have a table of restaurant menu items called menu_items.  This table has a float row(3,2) called price.  I have a query that looks like this:

 

$query = mysql_query("SELECT * FROM menu_items WHERE restaurant = '".$restaurantid."'");

 

How would I find the average of the price row from that query?  Basically I want to find the average item price for a restaurants menu.

[shlumph]

You can use MySQL's AVG function for that

 

SELECT AVG(price), * ...

 

http://www.tizag.com/mysqlTutorial/mysqlavg.php

[ShoeLace1291]

Hmm, I've actually been reading up on that function, but I was wondering how i display only the average of that query without having to loop through any results.

 

$query = mysql_query("SELECT * FROM restaurants ORDER BY id");
while($restaurants=mysql_fetch_array($query)){
     $query2 = mysql_query("SELECT AVG(price) as price FROM menu_items WHERE restaurant = '".$restaurant["id"]."'");
     echo "The average menu item price from ".$restaurant["name"]." is ".$average;
}

[Alex]

For that you would need to use a MySQL JOIN. Something like this should work:

 

SELECT restaurants.name, AVG(menu_items.price) as average_price FROM restaurants JOIN ON (restaurants.id = menu_items.restaurant)

 

[ot]Statistically speaking the mean of a set of data isn't usually important for this type of thing. More often the median is much more important.[/ot]

[ShoeLace1291]

I'm doing this in CodeIgniter, but I'm getting an SQL syntax error. This is my query:

$query = $this->db->query("SELECT restaurants.id,restaurants.name,restaurants.category,restaurants.ratePoints,restaurants.rateTimes,restaurants.smoking,restaurants.alcahol,avg(menu_items.price) as average_price FROM RESTAURANTS WHERE id = '".$id."' JOIN menu_items.price ON restaurants.id = menu_items.restaurant LIMIT 1");

[Alex]

SELECT restaurants.id, restaurants.name, restaurants.category, restaurants.ratePoints, restaurants.rateTimes, restaurants.smoking, restaurants.alcahol, avg(menu_items.price) as average_price FROM restaurants JOIN ON (restaurants.id = menu_items.restaurant) WHERE id = '$id' LIMIT 1

 

By the way, you have a typo in one of your column names, alcahol. And depending upon if that typo exists in the actual table or not you'll need to adjust that in the query.