How to Get the Average of All Rows of a Column of a MySQL Table Using PHP

In this article, we show how to get the average of all rows of a column of a MySQL table using PHP.

This could be very valuable for a wide number of applications.

Say, you're simply doing data analysis on customer sales. And you want to know the average price of the items that customers are buying. Using the MySQL AVG aggregate function, you can find the average of a complete column.

The general format of the PHP code to find the average of all the rows of a column is shown below.

$result= mysql_query("SELECT AVG(column_name) AS average FROM table"); $row = mysql_fetch_assoc($result); $average = $row['average']; echo ("This is the average: $average");

The above code finds the average from the column_name column from the table.

Example with Full PHP Code

Now we show an example of code that finds the average of all the rows of a column of a MySQL table.

Below is the table used for this example.

This represents a customer's order. For example, it can be a shopping cart and these are all the items in the shopping cart.

Below is the full PHP code to get the average of all the rows in the orders column of this table.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $result= mysql_query("SELECT AVG(orders) AS average FROM CustomerOrders"); $row = mysql_fetch_assoc($result); $average = $row['average']; echo ("This is the sum: $average"); ?>

So the above code finds the average of all the rows of the orders column.

Doing the math, adding up all the orders and dividing by the total number of items, we get 5.50 (5.50 + 7.00 + 9.00 + 2.25 + 3.75/5= 5.50).

So to explain the code a little, we first connect to the database.

Then we query the database using the $result variable. This $result variable queries the database by getting the AVG of the orders column AS average from the CustomerOrders table.

The average can be seen as the imaginary row that holds the average of all rows of the orders column.

We then use the mysql_fetch_assoc() function to get this row. We store it in the variable $row.

Lastly, we get the value in this average row and store it in the variable $average.

In the last line of the PHP code, we echo out the average.

Actual PHP Output