How to Get the Average of One Column in MySQL Database using PHP

Submitted by: 
Visitors have accessed this post 310 times.

Creating our Database

First, we are going to create our sample database.

I've included a SQL file in the downloadable of this tutorial. All you have to do is import the said file. If you have no idea on how to import, please visit my tutorial How import .sql file to restore MySQL database.

You should be able to create a database named dbtest.

Getting the Average

Finally, we get the average of our desired column. In this tutorial, we are going to display our table data for reference and we are going to get the average of "price" column.

Create a new file, name it as index.php and paste the codes below.

  1. <?php
  2. //connection
  3. $conn = new mysqli('localhost', 'root', '', 'dbtest');
  4. ?>
  5. <!DOCTYPE html>
  6. <html>
  7. <head>
  8. <meta charset="utf-8">
  9. <title>How to Get the Average of One Column in MySQL Database using PHP</title>
  10. </head>
  11. <body>
  13. <!-- displaying our table -->
  14. <table border="1">
  15. <thead>
  16. <th>ID</th>
  17. <th>Name</th>
  18. <th>Price</th>
  19. </thead>
  20. <tbody>
  21. <?php
  23. $sql = "SELECT * FROM products";
  24. $query = $conn->query($sql);
  26. while($row = $query->fetch_assoc()){
  27. echo "
  28. <tr>
  29. <td>".$row['id']."</td>
  30. <td>".$row['product_name']."</td>
  31. <td>".$row['product_price']."</td>
  32. </tr>
  33. ";
  34. }
  35. ?>
  36. </tbody>
  37. </table>
  39. <!-- getting the average -->
  40. <?php
  41. $sql = "SELECT AVG(product_price) AS average_price FROM products";
  42. $query1 = $conn->query($sql);
  43. $row1 = $query1->fetch_assoc();
  45. echo "Average Price: ".$row1['average_price'];
  46. ?>
  47. </body>
  48. </html>

That ends this tutorial. Happy Coding :)

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.