MySQL Aggregate Functions - AVG()

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:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

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 and MySQL Code:

<?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:

The average price of Clothing is $33.735000
The average price of Food is $8.730000
The average price of Music is $15.176667
The average price of Toy is $46.970000

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

Posted in: MySQL