Jump to content

MySQL Table Column Average


ShoeLace1291

Recommended Posts

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.

Link to comment
Share on other sites

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;
}

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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");

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.