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

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

This is a very valuable thing to be able to do because it has many applications.

Say, you have a MySQL table on customer orders. To find the sum of all the items that the customer has put in his or her shopping cart, you add up all of the prices of the items in the orders or prices column. This then gives the total price of the items before tax is computed (if there is tax).

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

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

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

Example with Full PHP Code

Now we show an example of code that adds up 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 sum 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 SUM(orders) AS totalsum FROM CustomerOrders"); $row = mysql_fetch_assoc($result); $sum = $row['totalsum']; echo ("This is the sum: $sum"); ?>

So the above code adds up all the rows of the orders column.

Doing the math, adding up all the orders, we get 27.50 (5.50 + 7.00 + 9.00 + 2.25 + 3.75= 27.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 SUM of the orders column AS totalsum from the CustomerOrders table.

The totalsum can be seen as the imaginary row that holds the sum 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 totalsum row and store it in the variable $sum.

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

Actual PHP Output