MySQL Aggregate Functions - AVG()

This lesson will teach you how to use the aggregate function AVG(). If you missed the Aggregate Introduction Lesson, please check it out now. It explains the meaning of aggregates and describes the GROUP BY statement.

The table we will be using is "products" and you can download the products.sql file so you can follow along. The table can be entered through you MySQL interface or through PHP.

If you are new to MySQL/PHP programming you will need to know how to Create a MySQL Table and Insert a MySQL Row.

Products Table:

MySQL Average - Finding a Middle Ground

The AVG function returns the average value for the specified column of a group.

Our imaginary customers have been complaining recently that our prices are too high, so we would like to find out the average price of each product type to see if this is in fact the truth.

To find out this metric we are going to apply the aggregate function to the price and GROUP BY type to create four price groups: Music, Toy, Clothing and Food.

<?php
// Make a MySQL Connection

$query = "SELECT type, AVG(price) FROM products GROUP BY type"; 
	 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo "The average price of ". $row['type']. " is $".$row['AVG(price)'];
	echo "<br />";
}
?>

Display:

Those prices seem very reasonable, in my opinion. I think our imaginary customers should change their view and keep buying products from us.

• Go Back
• Continue

Download Tizag.com's MySQL Book

If you would rather download the PDF of this tutorial, check out our MySQL eBook from the Tizag.com store. You may also be interested in getting the PHP eBook

Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!